Persistence quartiles — NTILE on per-HCP Cardiozin fill counts

Build pharma commercial SQL on your resume

Pharma commercial dataset modeled on IQVIA / Veeva / Komodo-shape Rx ledgers. 24-month window (2024-05-01 → 2026-04-30) covering a single-brand launch (Cardiozin, a new statin entering market 2025-09-01) plus 2-3 in-class competitors holding incumbent share, plus 5-6 unrelated products for portfolio breadth. Per-Rx-claim grain in fact_rx with is_new_rx flag (NRx vs TRx), days_supply, quantity, and copay. fact_calls captures rep activity at HCP grain with sample drops embedded. dim_hcp carries decile + target flag + specialty + territory_id; dim_rep is anchored to a territory; geography reaches Rx and calls only via the HCP join (a Hard-tier teaching point). All tables are net-new; no overlap with the provider, clinical-ops, clinical-trials, or payer-claims paths.

Practice the SQL patterns pharma commercial analysts run at Pfizer / Lilly / Novartis / Veeva: top prescribers by NRx, decile-based HCP segmentation, NRx vs TRx breakdowns, call-to-Rx attribution lag, territory market share HHI, switcher cohorts, persistence curves, and brand-launch performance scorecards. Per-Rx-claim grain on 24 months of data spanning a single-brand launch + in-class competitors. The patterns life-sciences commercial teams interview on.

Per-Rx-claim grain (NRx vs TRx flag, days_supply, copay), HCP/NPI dimension with decile + target flag + specialty, rep activity with sample drops, payer plans by formulary tier, and normalized territory FK that mirrors how real IQVIA / Veeva / Komodo datasets are shaped.

Authentic Pharma Commercial Schema

Single launch brand (Cardiozin, a new statin) entered market month 17 of a 24-month window; 2-3 in-class competitors hold incumbent share; 5-6 unrelated products provide portfolio breadth. Every mission can hook into 'how is our launch tracking vs. competition?' — the same question every brand team asks every Monday.

Launch + Competitor Narrative

Per-fill is_new_rx flag enables the canonical NRx (new prescriptions) vs TRx (total prescriptions including refills) split that drives every commercial dashboard. Days-supply distributions, refill-rate cohorts, and time-to-discontinuation curves — the patterns brand teams use to detect adherence and switching.

NRx, TRx & Persistence

Multi-CTE brand performance scorecards, rep effectiveness paired pivots (calls vs. Rx delta), territory market-share HHI, multi-touch attribution sequences (call → sample → Rx), and HCP segmentation capstones with multi-metric ranks. The SQL pharma commercial analytics teams interview on.

Resume-Grade Capstones

Cardiozin launched 8 months ago.

Active products by therapeutic class

HCPs in cardiology specialty

Rx fills per product

Calls per rep

Payer mix — Rx count per plan_type

Top 10 prescribers by Rx volume

NRx vs TRx breakdown per product

Quarterly call volume trend

Territory roster — HCPs per territory

Rep call activity vs target — surface zero-call reps

Decile-based prescriber tiers — recompute from Rx volume

High-NRx-share prescribers — HAVING threshold

Copay distribution per product — NTILE(4) quartiles

Top product per territory — RANK PARTITION

Call-to-Rx attribution lag — LAG window across event streams

HCP×product call-Rx ROI matrix

Switcher cohort — competitor-pre-launch to Cardiozin-post-launch

Territory market share HHI — concentration of Rx volume

Multi-touch attribution — sample-call vs plain-call → Rx

Brand performance scorecard — every product, every metric

Rep effectiveness — calls vs Rx delta paired pivot, ranked within district

Rx adherence proxy — fills per HCP×product across therapeutic class

Data quality audit — orphan NPIs + NULL plan_ids in one report

HCP segmentation capstone — multi-metric ranks + decile recompute + segment label

Persistence quartiles — NTILE on per-HCP Cardiozin fill counts

The Brief

You'll practice