Redshift: Grouping rows by range and adding to output columns

Question

I have data like this:

Table 1: (lots of items denoted by 1, 2, 3 etc. and with sales date in epochs and the number of sales on the given date as Number. The data only covers the last 12 weeks of sales)

Item | Sales_Date | Number   
1    1587633401000 2  
1  1587374201000  3  
1  1585732601000  4  
1  1583054201000  1  
1 1582190201000  2   
1 1580548601000  3

What I was as the output is a single line per item with each column showing the total sales for each individual month:

Output:

Item | Month_1_Sales | Month_2_Sales | Month_3_Sales  
1         3                 3               9

As the only sale that occurred happened at 1580548601000 (sales = 3), while 1583054201000 (sales = 1) and 1582190201000 (sales = 2) both occur in Month 2 etc.

So I need to split the sales dates into groups by month, sum their sales numbers, and then these numbers in columns. I am very new to SQL so don't know where to start. Would anyone be able to help?

How do you define month? 12 weeks are not 3 complete months. — Gordon Linoff
– Gordon Linoff, Commented Apr 23, 2020 at 11:37

Gordon Linoff · Accepted Answer · 2020-04-23 11:40:57Z

You can extract the months from the timestamp using:

select extract(month from (timestamp 'epoch' + sales_date / 1000 * interval '1 second'))

However, I am guessing that you really want 4-week periods, because 12 weeks of data is not 3 complete months. That would make more sense to me. For the calculation, use the difference from the earliest date and then use arithmetic and conditional aggregation:

select item,
       sum(case when floor((sales_date - min_sales_date) / (1000 * 60 * 60 * 24 * 4 * 7)) = 2
                then number
           end) as month_3_sales
       sum(case when floor((sales_date - min_sales_date) / (1000 * 60 * 60 * 24 * 4 * 7)) = 1
                then number
           end) as month_2_sales
       sum(case when floor((sales_date - min_sales_date) / (1000 * 60 * 60 * 24 * 4 * 7)) = 0
                then number
           end) as month_3_sales
from (select t1.*,
             min(sales_date) over () as min_sales_date
      from table1 t1
     ) t1
group by item;

Collectives™ on Stack Overflow

Redshift: Grouping rows by range and adding to output columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related