Your question is Second-Highest Sales Per Category. 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.
Sigmoid's retail analytics platform needs to identify the second-highest sales transaction within each store category for category-level performance reviews.
Write a PostgreSQL query that returns the second-highest distinct transaction amount for every retail category represented by a store. If multiple transactions share the second-highest amount, return all of them.
| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Unique retail category identifier |
| category_name | VARCHAR(100) | Retail category name |
| Column | Type | Description |
|---|---|---|
| store_idPK | INT | Unique store identifier |
| store_name | VARCHAR(100) | Store display name |
| category_id | INT | Category associated with the store |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique sales transaction identifier |
| store_id | INT | Store where the transaction occurred |
| transaction_date | DATE | Date of the transaction |
| amount | NUMERIC(10,2) | Transaction amount |