Products Priced Above Their Category Average
List products whose list price is above the average list price of their own category.
Expected output
category, name, price, category_avg (rounded to 2 decimals). Sorted by category, then price descending.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: E-commerce store
An online store selling electronics, home goods, books, apparel and beauty products to customers in six countries (Jan 2025 to Mar 2026).
customers
One row per registered customer.
| customer_id | INTEGER | primary key |
| name | TEXT | |
| country | TEXT | US, UK, DE, IN, BR or CA |
| signup_date | TEXT | YYYY-MM-DD |
| referred_by | INTEGER | customer_id of the referrer, NULL if none |
products
Product catalog with current list price.
| product_id | INTEGER | primary key |
| name | TEXT | |
| category | TEXT | |
| price | REAL | list price in USD |
orders
One row per order. Only completed orders count as revenue.
| order_id | INTEGER | primary key, increases with order_date |
| customer_id | INTEGER | |
| order_date | TEXT | YYYY-MM-DD |
| status | TEXT | completed, cancelled or returned |
order_items
Line items. Revenue of a line = quantity * unit_price (unit_price may include a discount).
| order_id | INTEGER | |
| product_id | INTEGER | |
| quantity | INTEGER | |
| unit_price | REAL | price actually charged per unit |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Average Order Value by Country (easy)
- Top 2 Products in Each Category (medium)
- Repeat Purchase Rate (medium)
- Days from Signup to First Order (medium)
- Running Revenue Total for 2025 (medium)