Count orders 12 months back from a certain month group by that month

redshift

I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So I need to convert the date to a format of yyyymm and count all order from selected month and sum up all orders that where made until 12 months back.

The outcome should look like:

I started with this query but it only counts orders for the selected month and not includes orders for former months.

SELECT TO_CHAR(create_date,'yyyymm') AS yyyymm,
       COUNT(order_id ) AS Number_of_orders_12_month_period
FROM Orders 
WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create_date) AND TO_CHAR(create_date,'yyyymm')  
group by 1

Best Answer

I don't have experience with Redshift but

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create__date) AND TO_CHAR(create_date,'yyyymm')

looks wrong to me. You don't want to compare create_date with itself, you want to compare it with current_date, and you want to compare after each date is converted to a month. For instance:

WHERE TO_CHAR(create_date,'yyyymm') BETWEEN TO_CHAR(DATEADD(MONTH,-12,current_date),'yyyymm') AND TO_CHAR(current_date,'yyyymm')

Related Solutions

Postgresql – How to select the rolling, most recent value for each person for each month in a range where that most recent value is less than 6 months old

2015-11-22 is not older than 6 months compared to 2015-05-01 unless you truncate the response date to month also.

> select '2015-11-22'::date + interval '6 months';
+---------------------+
| ?column?            |
|---------------------|
| 2016-05-22 00:00:00 |
+---------------------+

> select date_trunc('month', '2015-11-22'::date) + interval '6 months';
+---------------------------+
| ?column?                  |
|---------------------------|
| 2016-05-01 00:00:00+02:00 |
+---------------------------+

So depending the logic what you consider expired the query changes a bit. I've included both version. In comment the date and active condition as per the example.

Using Lateral join:

with users as (
    -- I assume you have user table so this can be omitted
    -- first and last are used to limit the join
    select user_id, min(date) as first, max(date) as last from responses group by user_id
), boundaries as (
    select 
        date_trunc('month', min(date)) as low 
        --, date_trunc('month', max(date)) as high 
        -- If you want to use the high in generate_series as upper boundary
    from responses
)
select user_id, tick as "date", score 
from 
    generate_series(
        (select low from boundaries), 
        date_trunc('month', current_date + interval '1 month'), interval '1 month'
    ) as tick
join users on tick.tick between users.first and users.last + interval '6 months'
-- # If you omit the users CTE and you have a users table
-- cross join users
left outer join lateral (
    select score from responses 
    where users.user_id = responses.user_id 
    -- # Proper 6 months calculation
    --and responses.date between tick.tick - interval '6 months' and tick.tick 
    -- # As example showed
    and responses.date < tick.tick and date_trunc('month', responses.date) + interval '6 months' > tick.tick 
    order by responses.date desc limit 1
) a on true
order by 1, 2;

Using Window functions:

with boundaries as (
    select 
        date_trunc('month', min(date)) as low
    from responses
)
select distinct responses.user_id, tick as "date", first_value(score) over (partition by responses.user_id, tick.tick order by responses.date desc)
from 
    generate_series(
        (select low from boundaries), 
        date_trunc('month', current_date + interval '1 month'), interval '1 month'
    ) as tick
join responses on (
    -- # Proper 6 months calculation
    -- responses.date between tick.tick - interval '6 months' and tick.tick 
    -- # As it was in the example
    responses.date < tick.tick and 
    date_trunc('month', responses.date) > tick.tick - interval '6 months'
) 
order by 1, 2;

SQL (Redshift) SELECT COUNT from CSV column

Was able to figure this out by unioning my individual count statements.

SELECT 'dog' as 'animal', count(case when vet.pets like '%dog%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
SELECT 'cat' as 'animal',  count(case when vet.pets like '%cat%' THEN 1 ELSE NULL END) AS 'count' FROM vet
UNION ALL
etc.