Get hands-on with dim_product — the product catalog that anchors every pharma commercial query. Practice basic single-table SELECT, GROUP BY with COUNT(*), and a two-level ORDER BY (descending on the metric, ascending on the dimension as a stable tiebreaker).
First day on the brand team — Priya asked me to learn the product portfolio before the Cardiozin launch readout next week. From dim_product, give me a count of how many products we carry per therapeutic class, sorted by product count descending so the most crowded class leads. Tiebreaker: alphabetical on therapeutic class so the order is stable when two classes tie at one product. Two columns: therapeutic_class and product_count.
| Column | Type | Key |
|---|---|---|
| product_id | INT | PK |
| brand_name | TEXT | |
| generic_name | TEXT | |
| therapeutic_class | TEXT | |
| is_launch_brand | INT | |
| launch_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Single-table aggregate. dim_product is the only table you need; group by the class column and count.
Group by the class column and COUNT(*). Riley's slide template expects the count alias as product_count.
You'll need a two-level sort to keep the order stable when classes tie at one product — descending on the count, ascending on the class name as tiebreaker.