1

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:

enter image description here

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
0

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')
  • It seems that by adding the changes just makes me get the same result as before, it showing a count of that month and not the month including all orders 12 months from that particular month which is what I want to achieve. – chocolatecosmos Apr 21 at 19:45
0

So I managed to fix it with this query

SELECT TO_CHAR(create__date,'yyyymm') as month_id
  , LAG(count(order_id), 0) OVER (ORDER BY create__date) +
    LAG(count(order_id), 1) OVER (ORDER BY create__date) +
    LAG(count(order_id), 2) OVER (ORDER BY create__date) +
    LAG(count(order_id), 3) OVER (ORDER BY create__date) +
    LAG(count(order_id), 4) OVER (ORDER BY create__date) +
    LAG(count(order_id), 5) OVER (ORDER BY create__date) +
    LAG(count(order_id), 6) OVER (ORDER BY create__date) +
    LAG(count(order_id), 7) OVER (ORDER BY create__date) +
    LAG(count(order_id), 8) OVER (ORDER BY create__date) +
    LAG(count(order_id), 9) OVER (ORDER BY create__date) +
    LAG(count(order_id), 10) OVER (ORDER BY create__date) +
    LAG(count(order_id), 11) OVER (ORDER BY last_day_create__date)  as Number_of_orders_12_month_period 
FROM orders GROUP BY create__date;

It looks weird but it gives the correct answer and is relativly fast

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.