Multiple Solutions to Data Scientist Interview Question From Amazon [Rolling Average]

[@TheRaju991]

Why didn't I find this channel sooner! I always love a good SQL challenge.

[@pushkarraj1663]

Great content Nate as always, but please keep em coming more frequently. Would love to contribute if that helps in releasing the videos faster

[@stratascratch]

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!

[@sanaayakurup5453]

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

[@stratascratch]

That is awesome! Thanks for sharing.

[@EverythingData98]

Thank you, watching these videos has helped me so much!

[@stratascratch]

Thanks for watching! Glad I could be of help.

[@purnimasharma71]

Why not you use left join in the first approach to get month 1 and 2 ?

[@adibzainal3991]

love this series

[@tamalchandra3427]

Nice collection of problems .. good stuff.

[@stratascratch]

Glad you enjoyed. Thanks.

[@user-ix7vs6vr7m]

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]

It's fixed. Thanks for letting me know!

[@jameshizon4861]

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! :)

[@stratascratch]

Good luck on the interview! Hope this channel helps.

[@jameshizon4861]

​@@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.

[@jayaa9730]

@james How was your interview with PayPal? I am also preparing for the interview with PayPal which requires sql skills

[@jameshizon4861]

@@jayaa9730 Got ghosted :(

[@caiyu538]

Great series.

[@johnchan544]

great content

[@joaopedroreissilva7075]

Really good content and also at least for me, really tough question.

[@stratascratch]

Thanks for watching! And it actually is a tough question for anyone but especially to have this question on an interview.

[@joaopedroreissilva7075]

@@stratascratch haha really good point!

[@sunnygawande5283]

what if i come with last solution only in an interview, then what can i say about optimization?

[@stratascratch]

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.

[@friendsplain]

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

[@stratascratch]

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.

[@VidyaBhandary]

Awesome !

[@GabeShortsz]

loving this

[@stratascratch]

Thanks for watching!

[@advisorita]

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.

[@stratascratch]

Yup! That works! I think I had this solution towards the end too, no?

[@advisorita]

@@stratascratch HAHA You are right. You got it! I literally commented half way in 😅

[@hemantsharma7986]

@@advisorita why would somebody do that... Lol

[@jerrywang1550]

good content

[@techiewithcamera]

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;

[@stratascratch]

Thank you for sharing!

[@YUVRAJSINGH-iz9gt]

Hello sir, I want to buy Lifetime plan. Can you please provide me Discount code

[@stratascratch]

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.

[@papilpatil]

LAG would have been easier?