amazon sql interview question

Amazon SQL Interview Question

by

Question: Assume you are given the below table on purchases from users. Write a query to get the number of users that purchased at least one product on multiple days.

Explanation & Definition: basically you are required to find those users having more than 1 purchase dates.

Difficulty Level: Easy, Source: NickSingh, Tag: Amazon SQL Interview Question

Assumptions: No NULLS present; DBMS: Spark SQL

Output Columns: num_users

Solution:

select count(*) as num_users
from(
	select user_id, count(distint to_date(purchase_time)) as num_days
	from purchases
	group by user_id
	having num_days >1)

Leave a Comment