Your question is Top Organic Products by Long-Term Members. 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.
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.
joined_at date.is_organic is TRUE.product_id as a deterministic tie-breaker.| 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 |
| 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 |
| 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 |