I’m Jacques and I’m taking the ‘SQL for Data Analyitcs’ course !
I’m struggling with the windows functions part. I don’t know if everyone has already done it?
I’m trying to calculate the 30-days rolling average for the daily number of sales deals. I’m pretty sure that my request is right, but apparently not haha! Please can someone have a look on my code? Thanks so much by advance!!!
with daily_sales as ( select sales_transaction_date::date , count(*) as total_sales_count from sales where extract(year from sales_transaction_date::date) = 2018 group by 1 order by 1 ), rolling_average as ( select sales_transaction_date , total_sales_count , avg(total_sales_count) over(order by sales_transaction_date rows between 30 preceding and current row) as deals_moving_average_30 , row_number() over(order by sales_transaction_date) as rank from daily_sales group by 1, 2 order by 1 ) select sales_transaction_date , case when rank >= 30 then deals_moving_average_30 else null end as deals_moving_average_30 from rolling_average order by 1;