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".

Playlists:

[praveensinghrathore]

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💛

[shubham]

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)<7; Solution using Lag()

[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

[observer]

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

[MOaz-hwqy]

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 <= 7 THEN user_id
ELSE NULL
END AS user_id_filtered
FROM purchase)
select distinct user_id_filtered FROM result ORDER by user_id_filtered

[oscararmandocisnerosruvalc]

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 <=7

GOT THIS CODE

[yuvrajmahendru]

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

[TuanTran-hkkj]

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

[HelloIamLauraa]

i didn't hear about this page yet

[Hamedali]

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] <= 7

[vistaarmein-vyom]

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

[malikabaymuradova]

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
BETWEEN 0 AND 7