Top Organic Products by Long-Term Members | Dataford Interview Questions

Thrive Market wants to understand which organic products are most popular with established members. A long-term member is defined as someone whose membership began at least 12 months before the order date.

Write a PostgreSQL query to find the top five most frequently purchased organic products by long-term members.

Requirements

Include only completed orders placed at least 12 months after the member's joined_at date.
Include only products where is_organic is TRUE.
Count distinct completed orders for each product, order from most to least purchased, and return the top five. Use product_id as a deterministic tie-breaker.

Schema

members

Column	Type	Description
member_idPK	INT	Unique Thrive Market member identifier
member_name	VARCHAR(100)	Member's display name
joined_at	DATE	Date the membership began
membership_status	VARCHAR(20)	Current membership status

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
member_id	INT	Member who placed the order
product_id	INT	Product purchased in the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order fulfillment status

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Product name shown in the Thrive Market catalog
is_organic	BOOLEAN	Whether the product is certified organic

Your question is Top Organic Products by Long-Term Members. 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