SQL Top Products Within 24 Hours

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Top Products Within 24 Hours. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Salesforce Marketing Cloud wants to identify which products attract users who convert shortly afterward. Write a PostgreSQL query that finds the top three products viewed by users who made a purchase within 24 hours after the view.

Requirements

Consider only interaction rows where interaction_type = 'view'.
A view qualifies when the same user has a purchase at or after the view and no more than 24 hours later. A view may qualify against multiple purchases, but count that view only once.
Aggregate by product and return the number of distinct qualifying users and qualifying views.
Rank products by distinct qualifying users descending, then qualifying views descending, then product_id ascending as a deterministic tie-breaker.
Return only the top three catalog products.

Schema

interactions

Column	Type	Description
event_idPK	INT	Unique interaction event identifier
user_id	INT	User who generated the interaction
product_id	INT	Viewed product identifier
interaction_type	VARCHAR(30)	Type of interaction, such as view or add_to_cart
occurred_at	TIMESTAMPTZ	Timestamp when the interaction occurred

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who made the purchase
product_id	INT	Purchased product identifier
purchased_at	TIMESTAMPTZ	Timestamp when the purchase occurred

products

Column	Type	Description
product_idPK	INT	Unique Salesforce product identifier
product_name	VARCHAR(120)	Product name in the catalog

Tablesinteractionspurchasesproducts

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

SQL Top Products Within 24 Hours

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Top Products Within 24 Hours. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

Requirements

Consider only interaction rows where interaction_type = 'view'.
A view qualifies when the same user has a purchase at or after the view and no more than 24 hours later. A view may qualify against multiple purchases, but count that view only once.
Aggregate by product and return the number of distinct qualifying users and qualifying views.
Rank products by distinct qualifying users descending, then qualifying views descending, then product_id ascending as a deterministic tie-breaker.
Return only the top three catalog products.

Schema

interactions

Column	Type	Description
event_idPK	INT	Unique interaction event identifier
user_id	INT	User who generated the interaction
product_id	INT	Viewed product identifier
interaction_type	VARCHAR(30)	Type of interaction, such as view or add_to_cart
occurred_at	TIMESTAMPTZ	Timestamp when the interaction occurred

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User who made the purchase
product_id	INT	Purchased product identifier
purchased_at	TIMESTAMPTZ	Timestamp when the purchase occurred

products

Column	Type	Description
product_idPK	INT	Unique Salesforce product identifier
product_name	VARCHAR(120)	Product name in the catalog

Tablesinteractionspurchasesproducts

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results