ZJAY ZJAY - 1 month ago 16
SQL Question

Select data with a rolling date criteria

The below query returns a distinct count of 'members' for a given month and brand (see image below).

select to_char(transaction_date, 'YYYY-MM') as month, brand,
count(distinct UNIQUE_MEM_ID) as distinct_count
from source.table
group by to_char(transaction_date, 'YYYY-MM'), brand;


The data is collected with a 15 day lag after the month closes (meaning September 2016 MONTHLY data won't be 100% until October 15). I am only concerned with monthly data.

The query I would like to build: Until the 15th of this month (October), last month's data (September) should reflect August's data. The current partial month (October) should default to the prior month and thus also to the above logic.

After the 15th of this month, last month's data (September) is now 100% and thus September should reflect September (and October will reflect September until November 15th, and so on).

The current partial month will always = the prior month. The complexity of the query is how to calc prior month.

This query will be ran on a rolling basis so needs to be dynamic.

To be clear, I am trying to build a query where distinct_count for the prior month (until end of current month + 15 days) should reflect (current month - 2) value (for each respective brand). After 15 days of the close of the month, prior month = (current month - 1).

Partial current month defaults to prior month's data. The 15 day value should be variable/modifiable.

enter image description here

Answer

First, simplify the query to:

select to_char(transaction_date, 'YYYY-MM') as month, brand,
       count(distinct members) as distinct_count 
from source.table
group by members, to_char(transaction_date, 'YYYY-MM'), brand;

Then, you are going to have a problem. The problem is that one row (say from Aug 20th) needs to go into two groups. A simple group by won't handle this. So, let's use union all. I think the result is something like this:

select date_trunc('month', transaction_date) as month, brand,
       count(distinct members) as distinct_count 
from source.table
where (date_trunc('month', transaction_date) < date_trunc('month' current_date) - interval '1 month') or
      (day(current_date) > 15 and date_trunc('month', transaction_date) = date_trunc('month' current_date) - interval '1 month')
group by date_trunc('month', transaction_date), brand
union all
select date_trunc('month' current_date) - interval '1 month' as month, brand,
       count(distinct members) as distinct_count 
from source.table
where (day(current_date) < 15 and date_trunc('month', transaction_date) = date_trunc('month' current_date) - interval '1 month')
group by brand;
Comments