loop over a date list (or any list) and append queries in mysql or snowflake

Question

I am new to sql language and recently snowflake. I have a table that contains all checkin dates for all users for a business

user_id | checkin_date
001       03-06-2018
001       07-07-2018
001       08-01-2018
002       03-19-2018
002       03-27-2018
002       07-11-2018

Now I want to do a query such that I can look back from a query_date to see how many times each user checked in between query_date - 7 and query_date, qyery_date - 90 and query date ... the following snowflake query does the job properly for query_date='2018-08-01'.

with user_checkin_history_sum as (
select 
  user_id,
  sum(iff(datediff(DAY, uc.checkin_date, '2018-08-01') <= 7, 1, 0)) as visits_past_7_days,
  sum(iff(datediff(DAY, uc.checkin_date, '2018-08-01') <= 90, 1, 0)) as visits_past_90_days,
from user_checkin as uc
where uc.checkin_date < '2018-08-01'
group by user_id
order by user_id
)

This gives me result

user_id | visits_past_7_days | visits_past_90_days
001       0                    2
002       0                    1

My question is, if I have more than one day as the query_date, i.e., I have a list of checkin_date, for each checkin_date in the list, I do the query as above and append all them together. Basically, it is a loop over + table append, but I do not find an answer how to do this in sql language. Essentially, what I want to do is like the following

with user_checkin_history_sum as (
select 
  user_id,
  sum(iff(datediff(DAY, uc.checkin_date, query_date) <= 7, 1, 0)) as visits_past_7_days,
  sum(iff(datediff(DAY, uc.checkin_date, query_date) <= 90, 1, 0)) as visits_past_90_days,
from user_checkin as uc
where uc.checkin_date < query_date and
LOOP OVER
    query_date in ('2018-08-01', '2018-06-01')
group by user_id
order by user_id
)

And hopefully it gives this result

user_id | query_date | visits_past_7_days | visits_past_90_days
001      '08-01-2018'  0                    2
002      '08-01-2018'  0                    1
001      '06-01-2018'  0                    1
002      '06-01-2018'  0                    2

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-09-27T06:02:26

You should be able to cross join a table containing all the dates you want to examine:

WITH dates AS (
    SELECT '2018-06-01' AS query_date UNION ALL
    SELECT '2018-08-01' UNION ALL
    ...     -- maybe other dates as well
),
user_checkin_history_sum AS (
    SELECT
        uc.user_id,
        d.query_date,
        SUM(IFF(DATEDIFF(DAY, uc.checkin_date, d.query_date) <= 7, 1, 0)) AS visits_past_7_days,
        SUM(IFF(DATEDIFF(DAY, uc.checkin_date, d.query_date) <= 90, 1, 0)) AS visits_past_90_days
    FROM dates d
    CROSS JOIN user_checkin AS uc
    WHERE uc.checkin_date < '2018-08-01'
    GROUP BY d.query_date, uc.user_id
    ORDER BY d.query_date, uc.user_id
)

loop over a date list (or any list) and append queries in mysql or snowflake

1 Answers