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

[@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)

[@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

[@sachin-b8c4m]

thank you

[@satviknaren9681]

Very much helpful !

[@ceyhunozturk5115]

Thanks Frederik

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

[@eb_reviews]

Thank you!

[@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

[@HelloIamLauraa]

i didn't hear about this page yet

[@frederikmuller]

you should give it a try!

[@HelloIamLauraa]

@@frederikmuller are there also nlp, ML questions or more like db and sql interview question?

[@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

[@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

[@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

[@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]