Pharma Commercial Analytics Path

Cardiozin launched 8 months ago.
Brand wants the territory scorecard by Friday.

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.

See All Missions

25 missions (10 free · 15 Pro incl. 5 Master) · 7 tables · IQVIA-style Rx + HCP + rep + payer schema

How It Works

1

Read the briefing

A Slack message from your manager

2

Explore the schema

5 tables in a star schema

3

Write your query

Full SQL editor with autocomplete

4

Get expert feedback

Graduated hints, not just pass/fail

Why This Path

Authentic Pharma Commercial Schema

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.

Launch + Competitor Narrative

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.

NRx, TRx & Persistence

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.

Resume-Grade Capstones

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.

The Missions

Each mission is a real request from someone at the company. Difficulty increases as you go.

Easy5 missions
1
Active products by therapeutic classFrom: Junior Analyst
HCPs in cardiology specialtyFrom: Junior Analyst
Rx fills per productFrom: Commercial Analytics Lead
Calls per repFrom: Sales Operations Manager
Payer mix — Rx count per plan_typeFrom: Market Access Director
Medium5 missions
Top 10 prescribers by Rx volumeFrom: Brand Director
NRx vs TRx breakdown per productFrom: Commercial Analytics Lead
Quarterly call volume trendFrom: Sales Operations Manager
Territory roster — HCPs per territoryFrom: District Sales Manager
Rep call activity vs target — surface zero-call repsFrom: Sales Operations Manager
Hard5 missions
Decile-based prescriber tiers — recompute from Rx volumeProFrom: Commercial Analytics Lead
High-NRx-share prescribers — HAVING thresholdProFrom: Forecasting Analyst
Copay distribution per product — NTILE(4) quartilesProFrom: Market Access Director
Top product per territory — RANK PARTITIONProFrom: District Sales Manager
Call-to-Rx attribution lag — LAG window across event streamsProFrom: Sales Operations Manager
Expert5 missions
HCP×product call-Rx ROI matrixProFrom: Brand Director
Switcher cohort — competitor-pre-launch to Cardiozin-post-launchProFrom: Forecasting Analyst
Territory market share HHI — concentration of Rx volumeProFrom: VP Commercial
Multi-touch attribution — sample-call vs plain-call → RxProFrom: Brand Director
Persistence quartiles — NTILE on per-HCP Cardiozin fill countsProFrom: Commercial Analytics Lead
Master5 missions
Brand performance scorecard — every product, every metricProFrom: VP Commercial
Rep effectiveness — calls vs Rx delta paired pivot, ranked within districtProFrom: Sales Operations Manager
Rx adherence proxy — fills per HCP×product across therapeutic classProFrom: Forecasting Analyst
Data quality audit — orphan NPIs + NULL plan_ids in one reportProFrom: Commercial Analytics Lead
HCP segmentation capstone — multi-metric ranks + decile recompute + segment labelProFrom: VP Commercial

The Database

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.

dim_hcp (150)dim_product (10)dim_geography (20)dim_payer_plan (10)dim_rep (15)fact_rx (~8,000)fact_calls (~3,000)

●dimension tables  ● fact tables

Build pharma commercial SQL on your resume

The patterns life-sciences brand teams interview on — on a real-shape Rx + HCP + rep dataset.

Looking for something different?

|