a little long but I used this!-select date,sum(total+second_lag+first_lag)/3 as rolling_avg from( select *, lag(total,2) over (order by date) as second_lag, lag(total) over (order by date) as first_lag from (select date_format(created_at,"%Y-%m") as date,sum(purchase_amt) as total from amazon_purchases where purchase_amt>=0 group by date) as a) as b group by date
Thanks! Been trying to add content more frequently but my schedule's been so hectic lately! Scripts are written but there's just not enough time to film it =/. More to come though!
Hi Nate. I was wondering if you were aware of an issue with the website where SQL questions won't fully load and when you try to run commands the only output is "'tuple' object has no attribute 'is_unique'". Not sure if it's on my end or sitewide
@@stratascratch Just additional commenting to help myself summarize info...Window function approach is also better in regards to readability. Thus, it is key for me personally to think about reducing amount of joins and instead using a window function in regards to performance and readability to easily catch errors when/if I need to debug.
Hello Nate, huge fan of your content! Just wondering, from your monthly revenue CTE, wouldn't applying LAG function to generate a last_month_revenue and 2mos_ago_revenue also give the 3 months of revenue needed to generate a 3 month rolling average? This would help avoid the situation of 2 self-joins to generate those columns
I think you're thinking about using the lag() function like this: WITH revenues AS ( SELECT to_date(to_char(created_at::date, 'YYYY-MM'),'YYY-MM-01') AS month_year, SUM(purchase_amt) AS revenue_month FROM amazon_purchases WHERE purchase_amt>=0 GROUP BY month_year ORDER BY month_year) SELECT *, (revenue_month + LAG(revenue_month, 1) OVER (ORDER BY month_year)+ LAG(revenue_month, 2) OVER (ORDER BY month_year))/3 AS rolling_avg FROM revenues The output is slightly different. It won't produce an average for the first two months. But it's not like it's a wrong solution either...it's just a minor nuance in the output.
I think this window function below can do the magic too: AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) Let me know your thoughts.
My Solution: with fin_table as (SELECT to_char(created_at, 'YYYY-MM') as year_month, sum(purchase_amt) as sum_purchase_amt, lag(sum(purchase_amt)) over (order by to_char(created_at, 'YYYY-MM')) as prev_month, lag(sum(purchase_amt), 2) over (order by to_char(created_at, 'YYYY-MM')) as prev_to_prev_month FROM amazon_purchases where purchase_amt>0 group by 1 order by to_char(created_at, 'YYYY-MM')) select year_month, round((sum_purchase_amt+prev_month+prev_to_prev_month)/3, 2) as rolling_average from fin_table where prev_month is not null and prev_to_prev_month is not null order by 1;
If you don't think it can be optimized then say so. Maybe talk about other ways of solving the question if the interviewers are interested. The goal is to communicate your knowledge on how SQL works.
Why didn't I find this channel sooner!
I always love a good SQL challenge.
a little long but I used this!-select date,sum(total+second_lag+first_lag)/3 as rolling_avg from(
select *, lag(total,2) over (order by date) as second_lag,
lag(total) over (order by date) as first_lag
from (select date_format(created_at,"%Y-%m") as date,sum(purchase_amt) as total from amazon_purchases where purchase_amt>=0 group by date) as a) as b
group by date
That is awesome! Thanks for sharing.
Great content Nate as always, but please keep em coming more frequently. Would love to contribute if that helps in releasing the videos faster
Thanks! Been trying to add content more frequently but my schedule's been so hectic lately! Scripts are written but there's just not enough time to film it =/. More to come though!
Thank you, watching these videos has helped me so much!
Thanks for watching! Glad I could be of help.
love this series
Hi Nate. I was wondering if you were aware of an issue with the website where SQL questions won't fully load and when you try to run commands the only output is "'tuple' object has no attribute 'is_unique'". Not sure if it's on my end or sitewide
It's fixed. Thanks for letting me know!
Nice collection of problems .. good stuff.
Glad you enjoyed. Thanks.
Keep it up! I have a possible PayPal contract DE role to prep for, so I will continue to look to your channel for SQL accountability/motivation! :)
Good luck on the interview! Hope this channel helps.
@@stratascratch Just additional commenting to help myself summarize info...Window function approach is also better in regards to readability. Thus, it is key for me personally to think about reducing amount of joins and instead using a window function in regards to performance and readability to easily catch errors when/if I need to debug.
@james How was your interview with PayPal? I am also preparing for the interview with PayPal which requires sql skills
@@jayaa9730 Got ghosted :(
Hello Nate, huge fan of your content!
Just wondering, from your monthly revenue CTE, wouldn't applying LAG function to generate a last_month_revenue and 2mos_ago_revenue also give the 3 months of revenue needed to generate a 3 month rolling average? This would help avoid the situation of 2 self-joins to generate those columns
I think you're thinking about using the lag() function like this:
WITH revenues AS (
SELECT to_date(to_char(created_at::date, 'YYYY-MM'),'YYY-MM-01') AS month_year,
SUM(purchase_amt) AS revenue_month
FROM amazon_purchases
WHERE purchase_amt>=0
GROUP BY month_year
ORDER BY month_year)
SELECT *,
(revenue_month + LAG(revenue_month, 1) OVER (ORDER BY month_year)+ LAG(revenue_month, 2) OVER (ORDER BY month_year))/3 AS rolling_avg
FROM revenues
The output is slightly different. It won't produce an average for the first two months. But it's not like it's a wrong solution either...it's just a minor nuance in the output.
Great series.
great content
Really good content and also at least for me, really tough question.
Thanks for watching! And it actually is a tough question for anyone but especially to have this question on an interview.
@@stratascratch haha really good point!
Awesome !
I think this window function below can do the magic too:
AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
Let me know your thoughts.
Yup! That works! I think I had this solution towards the end too, no?
@@stratascratch HAHA You are right. You got it! I literally commented half way in 😅
@@advisorita why would somebody do that... Lol
Why not you use left join in the first approach to get month 1 and 2 ?
loving this
Thanks for watching!
My Solution:
with fin_table as
(SELECT to_char(created_at, 'YYYY-MM') as year_month,
sum(purchase_amt) as sum_purchase_amt,
lag(sum(purchase_amt)) over (order by to_char(created_at, 'YYYY-MM')) as prev_month,
lag(sum(purchase_amt), 2) over (order by to_char(created_at, 'YYYY-MM')) as prev_to_prev_month
FROM amazon_purchases
where purchase_amt>0
group by 1
order by to_char(created_at, 'YYYY-MM'))
select year_month,
round((sum_purchase_amt+prev_month+prev_to_prev_month)/3, 2) as rolling_average
from fin_table
where prev_month is not null
and prev_to_prev_month is not null
order by 1;
Thank you for sharing!
good content
what if i come with last solution only in an interview, then what can i say about optimization?
If you don't think it can be optimized then say so. Maybe talk about other ways of solving the question if the interviewers are interested. The goal is to communicate your knowledge on how SQL works.
Hello sir, I want to buy Lifetime plan. Can you please provide me Discount code
That's great! Glad you're interested and find it useful. You can use `ss15` for 15% off the plans. Just add the code at checkout.
LAG would have been easier?