Top Products in First Week | Dataford Interview Questions

Blinkit wants to understand which products new users purchase soon after registration. Write a PostgreSQL query to identify the top five products purchased during each user's first seven days on the platform.

Requirements

Consider orders from registered_at through, but not including, registered_at + INTERVAL '7 days'.
Include only orders with status = 'completed' and non-null matching products.
Rank products by the number of distinct users who purchased them, descending.
Break ties by product_id ascending and return exactly the top five ranked products.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Blinkit user identifier
registered_at	TIMESTAMP	Timestamp when the user registered

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
user_id	INT	User who placed the order
product_id	INT	Purchased product identifier
ordered_at	TIMESTAMP	Timestamp when the order was placed
status	VARCHAR(20)	Order fulfillment status

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name shown in Blinkit

Your question is Top Products in First Week. Start with the requirements and the three tables in the Question tab.

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

Problem