# LAG and analytic COUNT

Just a quick post today to play around with some analytic functions.

The first time I talked to Tom Kyte, he asked me this question: What if you had a table that recorded patient visits by date and you wanted to compute the time between visits for each patient?

I fumbled it at the time, but soon realized this is really easy to answer with a very little use of analytic functions.

Let’s make a table and populate it:

```CREATE TABLE scott.patient_visits
(
patient_name varchar2(30),
visit_date DATE);

INSERT INTO scott.patient_visits (patient_name, visit_date)
(SELECT
substr(object_name,1,10) AS patient_name,
trunc(sysdate-MOD(object_id,365)) AS visit_date
FROM all_objects
WHERE object_name NOT LIKE '\_%'
AND object_name NOT LIKE '/%');
/```

And a quick look at the data:

```SELECT patient_name, visit_date FROM patient_visits
WHERE rownum < 20;```

Now that we have a table, we can use LAG to answer the question, and analytic COUNT to answer a few more besides:

```SELECT * FROM
( SELECT patient_name, visit_date
, to_char(visit_date,'WW') visit_wk
, lag(visit_date) OVER (partition BY patient_name ORDER BY patient_name, visit_date) last_visit
, visit_date - lag(visit_date) OVER (partition BY patient_name ORDER BY patient_name, visit_date) days_since
, COUNT(visit_id) OVER (partition BY patient_name) num_visits
, COUNT(visit_id) OVER (partition BY patient_name ORDER BY visit_date) nth_visit
, COUNT(visit_id) OVER (partition BY patient_name ORDER BY visit_date range 7 preceding) nth_visit_last_7d
, COUNT(visit_id) OVER (partition BY patient_name, to_number(to_char(visit_date,'WW'))) num_visits_this_wk
FROM patient_visits
) WHERE num_visits > 10
ORDER BY patient_name, visit_date;```

One thing I got out of this exercise was that I really like analytic COUNT. What are some of your favorite ways to use LAG or analytic COUNT?

This site uses Akismet to reduce spam. Learn how your comment data is processed.