`docs/example-queries.sql`

170 lines
-- Professional Directory — example queries
-- Run after Stage 1 (NPI ingestion). Each query honors opted_out = false.

-- ─── 1. Where are doctors setting up private practice? ──────────────────────
-- Top LA County ZIPs by physician count.
SELECT
  z.zip,
  z.city,
  COUNT(DISTINCT p.id)  AS physicians,
  COUNT(DISTINCT o.id)  AS orgs,
  COUNT(DISTINCT p.id) + COUNT(DISTINCT o.id) AS total_entities
FROM la_zips z
LEFT JOIN organizations o
       ON o.zip = z.zip AND o.opted_out = false
LEFT JOIN professional_locations pl
       ON pl.address ILIKE '%' || z.zip || '%'
LEFT JOIN professionals p
       ON p.id = pl.professional_id AND p.opted_out = false
GROUP BY z.zip, z.city
ORDER BY total_entities DESC
LIMIT 30;

-- ─── 2. Beverly Hills only ──────────────────────────────────────────────────
-- 90210 / 90211 / 90212 — all "Beverly Hills" in la_zips.
SELECT
  o.zip,
  COUNT(DISTINCT o.id)                                   AS orgs,
  COUNT(DISTINCT pl.professional_id)                     AS distinct_doctors,
  STRING_AGG(DISTINCT s.name, ', ' ORDER BY s.name)      AS specialties_present
FROM organizations o
LEFT JOIN professional_locations pl ON pl.organization_id = o.id
LEFT JOIN professionals p ON p.id = pl.professional_id
LEFT JOIN professional_specialties ps ON ps.professional_id = p.id
LEFT JOIN specialties s ON s.id = ps.specialty_id
WHERE o.zip IN ('90210','90211','90212')
  AND o.opted_out = false
GROUP BY o.zip
ORDER BY o.zip;

-- ─── 3. Specialty heat map by city ──────────────────────────────────────────
-- Where do dermatologists, cardiologists, plastic surgeons concentrate?
SELECT
  o.city,
  COUNT(*) FILTER (WHERE s.name ILIKE '%Dermatolog%')        AS dermatology,
  COUNT(*) FILTER (WHERE s.name ILIKE '%Cardio%')            AS cardiology,
  COUNT(*) FILTER (WHERE s.name ILIKE '%Plastic%')           AS plastic_surgery,
  COUNT(*) FILTER (WHERE s.name ILIKE '%Pediatric%')         AS pediatrics,
  COUNT(*) FILTER (WHERE s.name ILIKE '%Psych%')             AS psychiatry
FROM organizations o
LEFT JOIN professional_locations pl ON pl.organization_id = o.id
LEFT JOIN professional_specialties ps ON ps.professional_id = pl.professional_id
LEFT JOIN specialties s ON s.id = ps.specialty_id
WHERE o.opted_out = false
GROUP BY o.city
ORDER BY dermatology + cardiology + plastic_surgery DESC
LIMIT 20;

-- ─── 4. Solo practice vs group practice mix per city ───────────────────────
SELECT
  o.city,
  COUNT(*) FILTER (WHERE o.type = 'private_practice')   AS solo_practices,
  COUNT(*) FILTER (WHERE o.type = 'medical_group')      AS groups,
  COUNT(*) FILTER (WHERE o.type = 'hospital')           AS hospitals,
  COUNT(*) FILTER (WHERE o.type = 'urgent_care')        AS urgent_cares,
  COUNT(*) FILTER (WHERE o.type = 'surgery_center')     AS surgery_centers
FROM organizations o
WHERE o.opted_out = false AND o.county = 'Los Angeles'
GROUP BY o.city
ORDER BY solo_practices + groups DESC
LIMIT 30;

-- ─── 5. "Find me a top-confidence cardiologist in 90210" ────────────────────
SELECT
  p.full_name,
  p.npi_number,
  p.license_number,
  p.license_status,
  pl.address,
  pl.phone,
  o.name  AS practice_name,
  o.website,
  p.source_confidence_score
FROM professionals p
JOIN professional_specialties ps ON ps.professional_id = p.id
JOIN specialties s              ON s.id = ps.specialty_id
JOIN professional_locations pl  ON pl.professional_id = p.id
LEFT JOIN organizations o       ON o.id = pl.organization_id
WHERE p.opted_out = false
  AND s.name ILIKE '%Cardio%'
  AND (pl.address ILIKE '%90210%' OR o.zip IN ('90210','90211','90212'))
  AND p.source_confidence_score >= 0.85
ORDER BY p.source_confidence_score DESC, p.last_name;

-- ─── 6. SEO landing page: "Best dermatologists in West Hollywood" ──────────
SELECT
  p.full_name,
  p.medical_school,
  p.graduation_year,
  pl.address,
  pl.phone,
  o.website,
  p.profile_image_url,
  p.bio
FROM professionals p
JOIN professional_specialties ps ON ps.professional_id = p.id
JOIN specialties s              ON s.id = ps.specialty_id
JOIN professional_locations pl  ON pl.professional_id = p.id
LEFT JOIN organizations o       ON o.id = pl.organization_id
WHERE p.opted_out = false
  AND s.name ILIKE '%Dermatolog%'
  AND (pl.address ILIKE '%West Hollywood%' OR o.city = 'West Hollywood')
  AND p.license_status = 'Active'
ORDER BY p.source_confidence_score DESC NULLS LAST
LIMIT 25;

-- ─── 7. "Where are the hospitals, by ZIP density?" ─────────────────────────
SELECT
  o.zip,
  z.city,
  COUNT(*) AS hospitals,
  STRING_AGG(o.name, ' | ' ORDER BY o.name) AS hospital_names
FROM organizations o
LEFT JOIN la_zips z ON z.zip = o.zip
WHERE o.type = 'hospital' AND o.opted_out = false
GROUP BY o.zip, z.city
ORDER BY hospitals DESC, o.zip;

-- ─── 8. Doctors per capita proxy: practices per ZIP ────────────────────────
-- Rough density indicator. Pair with US Census ACS population data later.
SELECT
  o.zip,
  COUNT(*)                              AS practices,
  COUNT(DISTINCT pl.professional_id)    AS distinct_doctors,
  ROUND(
    COUNT(DISTINCT pl.professional_id)::NUMERIC
    / NULLIF(COUNT(*),0)::NUMERIC, 2
  ) AS doctors_per_practice
FROM organizations o
LEFT JOIN professional_locations pl ON pl.organization_id = o.id
WHERE o.opted_out = false AND o.zip IS NOT NULL
GROUP BY o.zip
HAVING COUNT(*) >= 5
ORDER BY practices DESC
LIMIT 50;

-- ─── 9. Recently licensed ──────────────────────────────────────────────────
-- Doctors enumerated in the past 3 years (NPI enumeration_date proxy).
SELECT
  EXTRACT(YEAR FROM p.license_issue_date) AS year_started,
  o.city,
  COUNT(*) AS new_doctors
FROM professionals p
LEFT JOIN professional_locations pl ON pl.professional_id = p.id
LEFT JOIN organizations o           ON o.id = pl.organization_id
WHERE p.license_issue_date >= NOW() - INTERVAL '3 years'
  AND p.opted_out = false
GROUP BY year_started, o.city
ORDER BY year_started DESC, new_doctors DESC;

-- ─── 10. Provenance audit — which sources did we pull each fact from? ─────
SELECT
  s.source_name,
  rr.entity_type,
  COUNT(*) AS records,
  MAX(rr.fetched_at) AS most_recent
FROM raw_records rr
JOIN sources s ON s.id = rr.source_id
GROUP BY s.source_name, rr.entity_type
ORDER BY s.source_name, rr.entity_type;