# Thread: Moving Window Query - Trailing Days in SQL

1. Registered User
Join Date
Dec 2011
Posts
3

## Unanswered: Moving Window Query - Trailing Days in SQL

I am having trouble writing a sql statement. Here is the scenario:

- My table contains the member_id and calendar_date that members of my social community logged in as follows:

(
Login_Key int NOT NULL AUTO_INCREMENT, -- unique identifier for the login event
Member_ID int NOT NULL, -- the member who logged in
Login_Date DATETIME NOT NULL -- the date they logged in on
)

I am trying to write a SQL query that will allow me to create a trend chart of the following:

- How many members who logged in during the past 7-14 days also logged in during the past 1-7 days
- For example (given today is Jan 14), if a member logged into the community during Jan 1 - 7, they would get counted if they also logged in during Jan 8 - 14.

Since this is a trend of percent of returning users over time, I believe I need to run a 7 trailing days calculation in SQL and also be able to calculate the denominator (all logins within the period) as well as the numerator (all who return during the next 7 days).

Here is some sample data that would be in the source table (DDL specified above):
1 123 12/27/11
2 123 12/27/11 (logged in twice on this date, could have multiple logins on a day but they we do not count more than the first)
3 123 1/12/12
4 123 1/13/12

In case this question is difficult to follow, here is an example of what the result set might look like (if it is slightly different, I can make some adjustments, group by, etc): * Record_No included for the discussion below *

Week_Ending_Date Member_ID Logged_In_Prior_Week Logged_In_Current_Week Record_No
1/10/12 123 YES NO 1 (prior week = 12/27/11 - 1/2/12; current week = 1/3/12 - 1/10/12)
1/11/12 123 NO NO 2 (prior week = 12/28/11 - 1/3/12; current week = 1/4/12 - 1/11/12)
1/12/12 123 NO YES 3 (prior week = 12/29/11 - 1/4/12; current week = 1/5/12 - 1/12/12)
1/13/12 123 NO YES 4 (prior week = 12/30/11 - 1/5/12; current week = 1/6/12 - 1/13/12)

I will count the records that meet my criteria (record 1) and then divide (Logged_In_Current_Week/Logged_In_Prior_Week) and trend it across all of the days (even though Member_ID 123 did not contribute to 1/1/12, 1/11/12, and 1/13/12, other members likely would.

This is not as simple as looking for the week portion of the date and grouping by that as each day the trend report would change (as opposed to changing every seven days). I think I need a trailing 7-day calculation or a moving window. I also need to keep this flexible as someone will invariably want the same calculation for a 30-day look back (with prior period = 60-30 days ago and current period = 30-1 days ago).

I have a DATE dimension table that has a record for each day if that helps. If this is not possible with a query, then I will have to write a cursor to load a table and read from that but it seems possible with a query.

Thank you for contributing some thoughts.

Cheers.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
you probably don't want an ANSI SQL solution

is it okay to request that this thread be moved to the MySQL forum?

3. Registered User
Join Date
Dec 2011
Posts
3
Hmmm... I don't think this is a problem that is specific to any DMBS platform. It is a query that should be able to be solved in the standard ANSI SQL. Does this make sense?

I just don't think this is a platform-specific query.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
ok, as you wish

just be advised that the ANSI window functions, like LAG specifically, will not be available in mysql

5. Registered User
Join Date
Dec 2011
Posts
3
Good point... I think it should be moved. Thanks for clarifying.

6. Registered User
Join Date
Nov 2003
Posts
2,988
An ANSI solution would probably involve something like

Code:
`count(*) over (partition by Member_ID order by Login_Date asc) rows between current row and 14 preceding)`
which would give you the number of logins for the last 14 rows for the "current row".

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483

Example 1:
Code:
```SELECT week_ending_date
, member_id
, COALESCE(
MAX(
CASE
BETWEEN DAYS(week_ending_date) - 13
AND DAYS(week_ending_date) -  7 THEN
'Yes'
END
)
, 'No'
) AS Logged_In_Prior_Week
, COALESCE(
MAX(
CASE
BETWEEN DAYS(week_ending_date) -  6
AND DAYS(week_ending_date)      THEN
'Yes'
END
)
, 'No'
) AS Logged_In_Current_Week
FROM  duration
GROUP BY
member_id
, week_ending_date
;```
Tested on Mimer SQL Developers - Mimer SQL-2003 Validator
Code:
```Result:

The following feature outside Core SQL-2003 is used:

F391, "Long identifiers"

The following vendor reserved word is used:

DAYS```
DAYS function in DB2
The result is 1 more than the number of days from January 1, 0001 to D, where D is the date that would occur if the DATE function were applied to the argument.
DAYS - IBM DB2 9.7 for Linux, UNIX, and Windows

Example 2: Tested on DB2 9.7.5 on Windows
Code:
```------------------------------ Commands Entered ------------------------------
WITH
VALUES
( 1 , 123 , '12/27/2011' )
, ( 2 , 123 , '12/27/2011' )
, ( 3 , 123 , '01/11/2012' )
, ( 4 , 123 , '01/12/2012' )
)
, Duration
(Week_Ending_Date) AS (
VALUES
'01/09/2012'
, '01/10/2012'
, '01/11/2012'
, '01/12/2012'
)
SELECT week_ending_date
, member_id
, COALESCE(
MAX(
CASE
BETWEEN DAYS(week_ending_date) - 13
AND DAYS(week_ending_date) -  7 THEN
'Yes'
END
)
, 'No'
) AS Logged_In_Prior_Week
, COALESCE(
MAX(
CASE
BETWEEN DAYS(week_ending_date) -  6
AND DAYS(week_ending_date)      THEN
'Yes'
END
)
, 'No'
) AS Logged_In_Current_Week
FROM  duration
GROUP BY
member_id
, week_ending_date
;```
Result was...
Code:
```------------------------------------------------------------------------------

WEEK_ENDING_DATE MEMBER_ID   LOGGED_IN_PRIOR_WEEK LOGGED_IN_CURRENT_WEEK
---------------- ----------- -------------------- ----------------------
01/09/2012               123 Yes                  No
01/10/2012               123 No                   No
01/11/2012               123 No                   Yes
01/12/2012               123 No                   Yes

4 record(s) selected.```
Example 2 was
Code:
```Result:

The following features outside Core SQL-2003 are used:

F641, "Row and table constructors"
F391, "Long identifiers"
T121, "WITH (excluding RECURSIVE) in query expression"
F661, "Simple tables"

The following vendor reserved word is used:

DAYS```

8. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Although the Example 3 worked on DB2 9.7.5 on Windows,
it looks like that SQL 2003 standard doesn't support a expression in GROUP BY clause.

Example 3:
Code:
```SELECT week_ending_date
, COALESCE(p.member_id , c.member_id , n.member_id) AS member_id
, CASE
WHEN COUNT(p.member_id) > 0 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Prior_Week
, CASE
WHEN COUNT(c.member_id) > 0 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Current_Week
FROM  duration      AS d
LEFT  OUTER JOIN
BETWEEN DAYS(d.week_ending_date) - 13
AND DAYS(d.week_ending_date) -  7
LEFT  OUTER JOIN
BETWEEN DAYS(d.week_ending_date) -  6
AND DAYS(d.week_ending_date)
LEFT  OUTER JOIN
ON  COALESCE(p.member_id , c.member_id) IS NULL
GROUP BY
week_ending_date
, COALESCE(p.member_id , c.member_id , n.member_id)
;
------------------------------------------------------------------------------

WEEK_ENDING_DATE MEMBER_ID   LOGGED_IN_PRIOR_WEEK LOGGED_IN_CURRENT_WEEK
---------------- ----------- -------------------- ----------------------
01/09/2012               123 Yes                  No
01/10/2012               123 No                   No
01/11/2012               123 No                   Yes
01/12/2012               123 No                   Yes

4 record(s) selected.```
Tested on Mimer SQL Developers - Mimer SQL-2003 Validator
Code:
```Result:

SELECT week_ending_date
, COALESCE(p.member_id , c.member_id , n.member_id) AS member_id
, CASE
WHEN COUNT(p.member_id) > 0 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Prior_Week
, CASE
WHEN COUNT(c.member_id) > 0 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Current_Week
FROM  duration      AS d
LEFT  OUTER JOIN
BETWEEN DAYS(d.week_ending_date) - 13
AND DAYS(d.week_ending_date) -  7
LEFT  OUTER JOIN
BETWEEN DAYS(d.week_ending_date) -  6
AND DAYS(d.week_ending_date)
LEFT  OUTER JOIN
ON  COALESCE(p.member_id , c.member_id) IS NULL
GROUP BY
COALESCE(p.member_id , c.member_id , n.member_id)
^-------
syntax error: COALESCE (
correction: (

, week_ending_date
;```

But, if moved the expression into FROM clause by using CROSS JOIN and LATERAL,
it conforms with SQL standard

Example 4:
The SQL-2003 Validator!

Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

Code:
```SELECT week_ending_date
, f.member_id
, CASE
WHEN COUNT(p.member_id) > 0 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Prior_Week
, CASE
WHEN COUNT(c.member_id) > 0 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Current_Week
FROM  duration      AS d
LEFT  OUTER JOIN
BETWEEN DAYS(d.week_ending_date) - 13
AND DAYS(d.week_ending_date) -  7
LEFT  OUTER JOIN
BETWEEN DAYS(d.week_ending_date) -  6
AND DAYS(d.week_ending_date)
LEFT  OUTER JOIN
ON  COALESCE(p.member_id , c.member_id) IS NULL
CROSS JOIN
LATERAL (
VALUES COALESCE(p.member_id , c.member_id , n.member_id)
) AS f(member_id)
GROUP BY
week_ending_date
, f.member_id
;```
Code:
```Result:

The following features outside Core SQL-2003 are used:

F391, "Long identifiers"
T491, "LATERAL derived table"
F401, "Extended joined table"
F661, "Simple tables"

The following vendor reserved word is used:

DAYS```
Last edited by tonkuma; 02-08-12 at 09:24. Reason: Remove Example 5

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Example 3 and Example 4 are not complete.
They wouldn't return some rows depending on data in login_history table.

Note: The rows which were not included in the results would satisfy "Logged_In_Prior_Week=No and Logged_In_Current_Week=No".
(Not all of the rows satisfied the condition. Some of them wouldn't be included.)

Another example.

Example 5:
Code:
```SELECT d.week_ending_date
, y.member_id
, CASE
) <= -7 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Prior_Week
, CASE
) >= -6 THEN
'Yes'
ELSE 'No'
END  AS Logged_In_Current_Week
FROM  duration      AS d
CROSS JOIN (
SELECT DISTINCT
member_id
)             AS y
LEFT  OUTER JOIN
ON  h.member_id = y.member_id
BETWEEN DAYS(d.week_ending_date) - 13
AND DAYS(d.week_ending_date)
GROUP BY
y.member_id
, d.week_ending_date
;```
Tested on Mimer SQL Developers - Mimer SQL-2003 Validator
Code:
```Result:

The following features outside Core SQL-2003 are used:

F391, "Long identifiers"
F591, "Derived tables"
F401, "Extended joined table"

The following vendor reserved word is used:

DAYS```

Test data for DB2 9.7.5 on Windows.
Code:
```SELECT * FROM login_history;
------------------------------------------------------------------------------

----------- ----------- ----------
1         123 12/27/2011
2         123 12/27/2011
3         123 01/11/2012
4         123 01/12/2012
11         456 12/26/2011
12         456 12/27/2011
13         456 01/12/2012
14         456 01/12/2012

8 record(s) selected.

SELECT * FROM duration;
------------------------------------------------------------------------------

WEEK_ENDING_DATE
----------------
01/09/2012
01/10/2012
01/11/2012
01/12/2012

4 record(s) selected.```
Result
Code:
```WEEK_ENDING_DATE MEMBER_ID   LOGGED_IN_PRIOR_WEEK LOGGED_IN_CURRENT_WEEK
---------------- ----------- -------------------- ----------------------
01/09/2012               123 Yes                  No
01/10/2012               123 No                   No
01/11/2012               123 No                   Yes
01/12/2012               123 No                   Yes
01/09/2012               456 Yes                  No
01/10/2012               456 No                   No
01/11/2012               456 No                   No                    <--- Not included in Example 3 nor Example 4
01/12/2012               456 No                   Yes

8 record(s) selected.```
Last edited by tonkuma; 02-08-12 at 10:10. Reason: Add Note for Example 3 and 4.