Amazon SQL Interview Question for Data Scientists and Data Analysts (StrataScratch 10322)

Solution and walkthrough of a real SQL interview question for Business Intelligence Engineer, Data Scientist and Data Analyst technical coding interviews. This question was asked by Amazon and is called "Finding User Purchases".
Try this question on StrataScratch: platform.stratascratch.com/co...
Check out another question from Amazon: • Another Amazon SQL Int...
Find me on LinkedIn: / frederikmueller
Playlists:
StrataScratch SQL Coding Questions: • StrataScratch Coding Q...
LeetCode database SQL problems: • LeetCode Database Prob...
LeetCode easy SQL problems: • LeetCode Easy Database...
LeetCode medium SQL problems: • LeetCode Medium Databa...
LeetCode hard SQL problems: • LeetCode Hard Database...

[@frederikmuller]

Try this question yourself: platform.stratascratch.com/coding/10322-finding-user-purchases?via=frederik

[@praveensinghrathore4542]

The fact that you solved it while recording and didn't practice it before hand, and still be able to deliver such a good explanation, KUDOS to you💛

[@SagarKumar-db2xy]

Underrated channel

[@shubham900100]

select user_id from (select *,lag(created_at) over(partition by user_id order by created_at) as la from amazon_transactions order by user_id) a group by user_id having min(created_at-la)

[@satviknaren9681]

Very much helpful !

[@ceyhunozturk5115]

Thanks Frederik

[@eb_reviews]

Thank you!

[@Info-Galaxy]

Oracle query for same WITH cte AS (SELECT user_id, order_id, order_date, FIRST_VALUE (order_date) OVER (PARTITION BY user_id ORDER BY order_date ASC) AS min_order_date FROM cust_orders) SELECT * FROM cte WHERE cte.order_Date - min_order_Date >= 7

[@ajtam05]

Yeah, I prefer CTEs. It's good to know self joins the way he's doing it (because a lot of existing code will look like that), but easier to understand and compartmentalize with CTE.

[@observer698]

Why did we need b.created_at >= a.created_at? since we already have the 7 day condition?

[@JuliaInMerica]

Or you can use Datediff function

[@yuvrajmahendru2149]

Is strata scratch better than leetcode for SQL and python questions?

[@frederikmuller]

If you’re preparing for Data Science interviews then StrataScratch, yes. Way more data questions. On Leetcode you can only do algorithmic questions with pythons, not data questions.

[@yuvrajmahendru2149]

@@frederikmuller but the python questions in strata scratch are only using pandas right? No general questions that are asked in interviews to test python knowledge?

[@MO3az-hw9qy]

WITH purchase AS ( SELECT user_id, created_at - LAG(created_at) OVER(PARTITION BY user_id ORDER BY created_at) AS difference FROM amazon_transactions ), result As (SELECT CASE WHEN difference >= 0 AND difference

[@TuanTran-hk8kj]

select user_id from (select user_id, min(datediff(created_at, pre_date)) as mindiff from (select user_id, created_at, lag(created_at, 1) over (partition by user_id order by created_at ) as pre_date from amazon_transactions) as df group by user_id) as df2 where mindiff < 8 I find this also produce the same result

[@oscararmandocisnerosruvalc8503]

WITH cte AS( select user_id,DATE_PART('day',created_at::date)-DATE_PART('day',Date_after::date) as days_between,created_at,Date_after from ( select user_id, created_at, LAG(created_at,1) OVER ( partition by user_id ORDER BY created_at) AS Date_after from amazon_transactions ) as x ) SELECT DISTINCT(user_id) FROM cte where days_between

[@malikabaymuradova2344]

why does this give an incorrect answer? there are three numbers included in my output that should not be in the answer with ranked_transactions as ( select *, row_number() over (partition by user_id order by created_at) as rnk from amazon_transactions ), first_transactions as ( select * from ranked_transactions where rnk = 1) select distinct t1.user_id from first_transactions t1 join amazon_transactions t2 on t1.user_id = t2.user_id where t1.id t2.id and t2.created_at::date-t1.created_at::date BETWEEN 0 AND 7

[@wowwithdeeksha3947]

please let me know if the below query is correct? SELECT cust_id FROM amazon_transactions WHERE DATEDIFF( Date, LAG(Date) )> 7 GROUP BY cust_id Date PARTITION BY cust_id

[@Hamedali92]

I've used the Lead function and it works: CREATE TABLE #AmazonTransactions( [ID] [int] NULL, [UserID] [int] NULL, [Item] [varchar](50) NULL, [Created_at] [Date] NULL, [Revenue] [int] NULL ) INSERT INTO #AmazonTransactions VALUES(1,101,'Milk','7/1/2022',100) INSERT INTO #AmazonTransactions VALUES(2,101,'Milk','7/5/2022',100) INSERT INTO #AmazonTransactions VALUES(2,101,'Milk','7/15/2022',100) INSERT INTO #AmazonTransactions VALUES(3,102,'Chocolate','7/10/2022',50) INSERT INTO #AmazonTransactions VALUES(4,103,'Biscuit','7/16/2022',20) INSERT INTO #AmazonTransactions VALUES(4,103,'Biscuit','7/20/2022',20) select x.UserID from ( select UserID --,Created_at --,LEAD(Created_at) over (partition by UserID order by Created_at) ,DATEDIFF(DD,Created_at,LEAD(Created_at) over (partition by UserID order by Created_at) ) AS [No. of Days] from #AmazonTransactions A ) x WHERE x.[No. of Days]