Conditional aggregation via SUM(CASE WHEN ...) — splitting one row source into two metrics in a single SELECT. The NRx (new prescriptions) vs TRx (total Rx including refills) breakdown is the canonical pharma commercial split; every brand dashboard carries both.
Wednesday scorecard refresh — Marcus wants the NRx-vs-refill split per product on the slide tomorrow. From fact_rx joined to dim_product, give me four metrics per brand: nrx (count where is_new_rx=1), trx_refill (count where is_new_rx=0), total (all rows), and nrx_pct (the new-Rx share, rounded to 1 decimal). Use SUM(CASE WHEN ...) for the conditional counts — don't write two separate queries and join them. Five output columns: brand_name, nrx, trx_refill, total, nrx_pct. Sort by total descending. Float-promote the percentage so you don't get integer division — multiply by 100.0 (with the decimal), not 100.
| Column | Type | Key |
|---|---|---|
| rx_id | INT | PK |
| hcp_npi | TEXT | FK → dim_hcp |
| product_id | INT | FK → dim_product |
| plan_id | INT | FK → dim_payer_plan |
| fill_date | TEXT | |
| is_new_rx | INT | |
| days_supply | INT | |
| quantity | INT | |
| copay | REAL |
| 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 GROUP BY on brand_name with two SUM(CASE WHEN ...) aggregates plus COUNT(*). The CASE expression returns 1 when the condition matches and 0 otherwise; SUM totals the 1s.
is_new_rx is INTEGER (1 or 0) — don't compare to the string 'true' or 'new'. One conditional aggregate counts the 1s (NRx); a sibling conditional aggregate counts the 0s (refills); COUNT(*) gives the total.
For the nrx_pct ratio, multiply by 100.0 (the decimal!) BEFORE dividing — without the decimal, integer division silently rounds percentages to whole-number multiples. Sort descending on total.