Ask IIB in all four verticals

SELECT SUM(policies_in_force) AS policies_in_force
FROM srv_policies_monthly
WHERE month = DATE '2026-09-01'
LIMIT 1

SELECT state_name, SUM(claims) AS claims
FROM srv_claim_frequency
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY state_name
ORDER BY claims DESC
LIMIT 5

SELECT CASE WHEN month >= DATE '2026-07-01' THEN 'Jul to Sep 2026' ELSE 'Jul to Sep 2025' END AS period,
       SUM(claims) AS claims
FROM srv_claim_frequency
WHERE state_code = 'TS' AND vehicle_class = '2W'
  AND (month BETWEEN DATE '2026-07-01' AND DATE '2026-09-01' OR month BETWEEN DATE '2025-07-01' AND DATE '2025-09-01')
GROUP BY 1
ORDER BY 1
LIMIT 200

SELECT rto_code, SUM(claims) AS theft_claims
FROM srv_claims_weekly
WHERE rto_code IN ('TS09', 'TS10', 'TS11', 'TS12', 'TS13') AND claim_type = 'THEFT'
  AND week_start BETWEEN DATE '2026-08-24' AND DATE '2026-09-28'
GROUP BY rto_code
ORDER BY theft_claims DESC, rto_code
LIMIT 10

SELECT state_name, SUM(claims) AS claims, ROUND(SUM(claim_amount) / SUM(claims)) AS avg_claim_amount
FROM srv_claim_frequency
WHERE vehicle_class = 'PCAR' AND claim_type = 'OD' AND month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY state_name
ORDER BY avg_claim_amount DESC
LIMIT 10

SELECT vehicle_class, ROUND(SUM(premium)) AS premium
FROM srv_policies_monthly
WHERE month >= DATE '2026-01-01'
GROUP BY vehicle_class
ORDER BY premium DESC
LIMIT 200

SELECT month, SUM(new_policies) AS new_policies
FROM srv_policies_monthly
WHERE state_code = 'TS' AND month >= DATE '2026-01-01'
GROUP BY month
ORDER BY month
LIMIT 200

WITH m AS (
  SELECT state_name, month, SUM(claims) AS claims, SUM(policies_in_force) AS pif
  FROM srv_claim_frequency
  WHERE claim_type = 'THEFT' AND month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
  GROUP BY state_name, month
)
SELECT state_name, SUM(claims) AS theft_claims, ROUND(SUM(claims) * 1000.0 / AVG(pif), 2) AS theft_claims_per_1000_policies
FROM m
GROUP BY state_name
ORDER BY theft_claims_per_1000_policies DESC
LIMIT 5

SELECT vehicle_class, SUM(registered_stock) AS registered_stock
FROM srv_uninsured_estimate
WHERE state_code = 'RJ'
GROUP BY vehicle_class
ORDER BY registered_stock DESC
LIMIT 200

SELECT state_name, year, mv_theft_cases
FROM srv_theft_ncrb
WHERE year = (SELECT MAX(year) FROM srv_theft_ncrb)
ORDER BY mv_theft_cases DESC
LIMIT 5

SELECT year, SUM(killed) AS killed
FROM srv_accidents
WHERE state_code = 'TS'
GROUP BY year
ORDER BY year
LIMIT 200

SELECT CASE WHEN month >= DATE '2026-07-01' THEN 'Jul to Sep 2026' ELSE 'Jul to Sep 2025' END AS period,
       SUM(claims) AS tp_claims
FROM srv_claim_frequency
WHERE state_code = 'MH' AND claim_type = 'TP'
  AND (month BETWEEN DATE '2026-07-01' AND DATE '2026-09-01' OR month BETWEEN DATE '2025-07-01' AND DATE '2025-09-01')
GROUP BY 1
ORDER BY 1
LIMIT 200

SELECT state_name, ROUND(insured_share * 100, 1) AS insured_share
FROM srv_uninsured_estimate
WHERE state_code = 'RJ' AND vehicle_class = '2W'
LIMIT 1

SELECT insurer_code, SUM(claims) AS claims
FROM srv_claims_weekly
WHERE cycle = '2026-09'
GROUP BY insurer_code
ORDER BY claims DESC
LIMIT 10

SELECT claim_type, ROUND(SUM(claim_amount)) AS claim_amount, ROUND(SUM(paid_amount)) AS paid_amount,
       ROUND(SUM(paid_amount) * 100.0 / SUM(claim_amount), 1) AS paid_share_pct
FROM srv_claims_weekly
WHERE cycle BETWEEN '2025-10' AND '2026-09'
GROUP BY claim_type
ORDER BY claim_type
LIMIT 200

SELECT month, SUM(claims) AS od_claims
FROM srv_claim_frequency
WHERE vehicle_class = 'GCV' AND claim_type = 'OD' AND month >= DATE '2026-01-01'
GROUP BY month
ORDER BY month
LIMIT 200

SELECT rto_code, SUM(policies_in_force) AS policies_in_force
FROM srv_policies_monthly
WHERE state_code = 'TS' AND vehicle_class = '2W' AND month = DATE '2026-09-01'
GROUP BY rto_code
ORDER BY policies_in_force DESC
LIMIT 10

SELECT cover_type, SUM(new_policies) AS new_policies, ROUND(SUM(premium) / SUM(new_policies)) AS avg_premium
FROM srv_policies_monthly
WHERE vehicle_class = 'PCAR' AND month >= DATE '2026-01-01'
GROUP BY cover_type
ORDER BY avg_premium DESC
LIMIT 200

SELECT week_start, SUM(claims) AS theft_claims
FROM srv_claims_weekly
WHERE state_code = 'TS' AND claim_type = 'THEFT' AND week_start BETWEEN DATE '2026-07-13' AND DATE '2026-09-28'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT year, SUM(registrations) AS registrations
FROM srv_registrations
WHERE year > (SELECT MAX(year) FROM srv_registrations) - 5
GROUP BY year
ORDER BY year
LIMIT 200

SELECT month, SUM(claims) AS claims
FROM srv_health_claims_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY month
ORDER BY month
LIMIT 200

SELECT hospital_name, SUM(claims_discharged) AS discharged_claims,
       ROUND(SUM(billed_amount) / SUM(peer_billed_amount), 2) AS cost_vs_peers
FROM srv_health_hospital_monthly
WHERE month BETWEEN DATE '2026-06-01' AND DATE '2026-09-01'
GROUP BY hospital_name
HAVING SUM(claims_discharged) >= 20
ORDER BY cost_vs_peers DESC
LIMIT 10

SELECT hospital_name, SUM(claims_discharged) AS discharged_claims,
       ROUND(SUM(avg_los_days * claims_discharged) / SUM(claims_discharged), 2) AS avg_stay_days
FROM srv_health_hospital_monthly
WHERE month BETWEEN DATE '2026-06-01' AND DATE '2026-09-01'
GROUP BY hospital_name
HAVING SUM(claims_discharged) >= 50
ORDER BY avg_stay_days DESC
LIMIT 10

SELECT hospital_name, SUM(claims) AS claims
FROM srv_health_hospital_procedures
WHERE procedure_code IN ('CARD-CABG', 'CARD-PTCA') AND NOT has_required_capability
GROUP BY hospital_name
ORDER BY claims DESC
LIMIT 10

SELECT procedure_code, SUM(claims) AS claims, COUNT(*) AS hospitals
FROM srv_health_hospital_procedures
WHERE NOT has_required_capability
GROUP BY procedure_code
ORDER BY claims DESC
LIMIT 200

SELECT week_start, SUM(dengue_claims) AS dengue_claims
FROM srv_health_weather_weekly
WHERE district_code = 'WB-KOL' AND week_start BETWEEN DATE '2026-07-13' AND DATE '2026-09-28'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT district, SUM(dengue_claims) AS dengue_claims
FROM srv_health_weather_weekly
WHERE week_start BETWEEN DATE '2026-08-24' AND DATE '2026-09-28'
GROUP BY district
ORDER BY dengue_claims DESC
LIMIT 3

SELECT week_start, SUM(rainfall_mm) AS rainfall_mm, SUM(dengue_claims) AS dengue_claims
FROM srv_health_weather_weekly
WHERE district_code = 'UP-LKO' AND week_start >= DATE '2026-07-01'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT product_name, claims_settled, ROUND(rejected_share * 100, 1) AS rejected_share_pct
FROM srv_health_products
WHERE claims_settled >= 300
ORDER BY rejected_share DESC
LIMIT 5

SELECT ped_wait_months, SUM(claims_settled) AS claims_settled,
       ROUND((SUM(claims_settled_part) + SUM(claims_rejected)) * 100.0 / SUM(claims_settled), 1) AS cut_or_rejected_pct
FROM srv_health_products
GROUP BY ped_wait_months
ORDER BY ped_wait_months
LIMIT 200

SELECT room_rent_limit, SUM(claims_settled) AS claims_settled,
       ROUND((SUM(claims_settled_part) + SUM(claims_rejected)) * 100.0 / SUM(claims_settled), 1) AS cut_or_rejected_pct
FROM srv_health_products
GROUP BY room_rent_limit
ORDER BY cut_or_rejected_pct DESC
LIMIT 200

SELECT SUM(claims) AS claims, SUM(claims_cashless) AS cashless_claims,
       ROUND(SUM(claims_cashless) * 100.0 / SUM(claims), 1) AS cashless_share_pct
FROM srv_health_claims_monthly
WHERE state_code = 'MH' AND month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
LIMIT 1

SELECT hospital_type, SUM(claims_discharged) AS discharged_claims,
       ROUND(SUM(billed_amount) / SUM(claims_discharged)) AS cost_per_admission
FROM srv_health_claims_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY hospital_type
ORDER BY cost_per_admission DESC
LIMIT 200

SELECT district, SUM(claims) AS claims
FROM srv_health_claims_monthly
WHERE month >= DATE '2026-01-01'
GROUP BY district
ORDER BY claims DESC
LIMIT 10

SELECT insurer_code, SUM(claims - claims_open) AS settled_claims, SUM(claims_rejected) AS rejected_claims,
       ROUND(SUM(claims_rejected) * 100.0 / SUM(claims - claims_open), 1) AS rejection_rate_pct
FROM srv_health_claims_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY insurer_code
ORDER BY rejection_rate_pct DESC
LIMIT 5

SELECT state_name, COUNT(*) AS hospitals, COUNT(*) FILTER (WHERE nabh) AS nabh_hospitals
FROM srv_health_hospitals
GROUP BY state_name
ORDER BY nabh_hospitals DESC, state_name
LIMIT 200

SELECT tier, COUNT(*) AS hospitals
FROM srv_health_hospitals
WHERE list_contains(string_split(capabilities, '|'), 'cath_lab')
GROUP BY tier
ORDER BY tier
LIMIT 200

SELECT procedure_name, avg_los_days
FROM srv_health_procedures
ORDER BY avg_los_days DESC
LIMIT 8

SELECT state_name, SUM(malaria_claims) AS malaria_claims
FROM srv_health_weather_weekly
WHERE week_start >= DATE '2026-01-01'
GROUP BY state_name
ORDER BY malaria_claims DESC
LIMIT 5

SELECT CASE WHEN month >= DATE '2026-07-01' THEN 'Jul to Sep 2026' ELSE 'Jul to Sep 2025' END AS period,
       SUM(claims) AS claims
FROM srv_health_claims_monthly
WHERE state_code = 'TS'
  AND (month BETWEEN DATE '2026-07-01' AND DATE '2026-09-01' OR month BETWEEN DATE '2025-07-01' AND DATE '2025-09-01')
GROUP BY 1
ORDER BY 1
LIMIT 200

SELECT SUM(policies_in_force) AS policies_in_force
FROM srv_life_policies_monthly
WHERE month = DATE '2026-09-01'
LIMIT 1

SELECT district_name, SUM(early_claims) AS early_claims
FROM srv_life_death_claims_monthly
WHERE month BETWEEN DATE '2026-05-01' AND DATE '2026-09-01'
GROUP BY district_name
ORDER BY early_claims DESC
LIMIT 2

SELECT month, SUM(early_claims) AS early_claims
FROM srv_life_death_claims_monthly
WHERE state_code = 'BR' AND channel = 'pos' AND month >= DATE '2026-01-01'
GROUP BY month
ORDER BY month
LIMIT 200

SELECT agent_id, early_death_claims
FROM srv_life_agents
WHERE registry = 'pos'
ORDER BY early_death_claims DESC, agent_id
LIMIT 12

SELECT COUNT(DISTINCT insurer_code) AS insurers
FROM srv_life_death_claims_monthly
WHERE district_code IN ('BR-GAY', 'BR-NAL') AND month BETWEEN DATE '2026-05-01' AND DATE '2026-09-01' AND early_claims > 0
LIMIT 1

SELECT SUM(claims) AS claims, SUM(non_medical_claims) AS non_medical_claims,
       ROUND(SUM(non_medical_claims) * 100.0 / SUM(claims), 1) AS non_medical_share_pct
FROM srv_life_death_claims_monthly
WHERE district_code IN ('BR-GAY', 'BR-NAL') AND month BETWEEN DATE '2026-05-01' AND DATE '2026-09-01'
LIMIT 1

SELECT channel, SUM(paid) AS paid, SUM(repudiated) AS repudiated,
       ROUND(SUM(repudiated) * 100.0 / NULLIF(SUM(paid) + SUM(repudiated), 0), 1) AS repudiation_rate_pct
FROM srv_life_death_claims_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY channel
ORDER BY repudiation_rate_pct DESC
LIMIT 200

SELECT channel, SUM(policies) AS policies,
       ROUND(SUM(lapsed_within_1_year) * 100.0 / SUM(policies), 1) AS lapse_rate_pct
FROM srv_life_cohorts
WHERE product_type = 'ulip' AND full_year_observed
GROUP BY channel
ORDER BY lapse_rate_pct DESC
LIMIT 200

SELECT age_band, SUM(policies) AS policies
FROM srv_life_cohorts
WHERE product_type = 'ulip' AND channel = 'bancassurance'
GROUP BY age_band
ORDER BY age_band
LIMIT 200

SELECT SUM(policies) AS policies,
       SUM(policies) FILTER (WHERE age_band IN ('55 to 64', '65 and over')) AS policies_55_plus,
       ROUND(SUM(policies) FILTER (WHERE age_band IN ('55 to 64', '65 and over')) * 100.0 / SUM(policies), 1) AS share_55_plus_pct
FROM srv_life_cohorts
WHERE product_type = 'ulip' AND channel = 'bancassurance'
LIMIT 1

SELECT channel, SUM(new_policies) AS new_policies
FROM srv_life_policies_monthly
WHERE month >= DATE '2026-01-01'
GROUP BY channel
ORDER BY new_policies DESC
LIMIT 200

SELECT month, SUM(lapses) AS lapses, SUM(surrenders) AS surrenders
FROM srv_life_policies_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY month
ORDER BY month
LIMIT 200

SELECT product_type, ROUND(SUM(sum_assured_in_force)) AS sum_assured_in_force
FROM srv_life_policies_monthly
WHERE month = DATE '2026-09-01'
GROUP BY product_type
ORDER BY sum_assured_in_force DESC
LIMIT 200

SELECT channel, SUM(new_policies) AS new_policies, ROUND(SUM(sum_assured) / SUM(new_policies)) AS avg_sum_assured
FROM srv_life_policies_monthly
WHERE product_type = 'term' AND month >= DATE '2026-01-01'
GROUP BY channel
ORDER BY avg_sum_assured DESC
LIMIT 200

SELECT cause_group, SUM(claims) AS claims
FROM srv_life_death_claims_monthly
WHERE duration_band = 'under 1 year' AND month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY cause_group
ORDER BY claims DESC
LIMIT 200

SELECT channel, SUM(late_intimations) AS late_intimations, SUM(claims) AS claims
FROM srv_life_death_claims_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY channel
ORDER BY late_intimations DESC
LIMIT 200

SELECT week_start, SUM(claims) AS claims
FROM srv_life_death_claims_weekly
WHERE week_start BETWEEN DATE '2026-07-13' AND DATE '2026-09-28'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT registry, status, COUNT(*) AS agents
FROM srv_life_agents
GROUP BY registry, status
ORDER BY registry, status
LIMIT 200

SELECT insurer_code, ROUND(SUM(annual_premium)) AS annual_premium
FROM srv_life_policies_monthly
WHERE month >= DATE '2026-01-01'
GROUP BY insurer_code
ORDER BY annual_premium DESC
LIMIT 5

SELECT SUM(paid) AS paid, SUM(repudiated) AS repudiated, SUM(under_investigation) AS under_investigation
FROM srv_life_death_claims_monthly
WHERE channel = 'pos' AND month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
LIMIT 1

SELECT SUM(policies_in_force) AS policies_in_force, ROUND(SUM(sum_insured)) AS sum_insured
FROM srv_property_exposure_district
LIMIT 1

SELECT district, property_type,
       COALESCE(SUM(sum_insured) FILTER (WHERE week_start = DATE '2026-09-28'), 0)
         - COALESCE(SUM(sum_insured) FILTER (WHERE week_start = DATE '2026-08-03'), 0) AS rise_in_sum_insured
FROM srv_property_exposure_weekly
WHERE week_start IN (DATE '2026-08-03', DATE '2026-09-28')
GROUP BY district, property_type
ORDER BY rise_in_sum_insured DESC
LIMIT 10

SELECT week_start, ROUND(SUM(sum_insured)) AS sum_insured
FROM srv_property_exposure_weekly
WHERE district_code = 'MH-THN' AND property_type = 'warehouse' AND week_start BETWEEN DATE '2026-07-13' AND DATE '2026-09-28'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT SUM(new_policies) AS new_policies, ROUND(SUM(new_sum_insured)) AS new_sum_insured
FROM srv_property_exposure_weekly
WHERE district_code = 'MH-THN' AND property_type = 'warehouse' AND week_start BETWEEN DATE '2026-08-10' AND DATE '2026-09-28'
LIMIT 1

SELECT district, SUM(claims) AS flood_claims
FROM srv_property_claims_weekly
WHERE state_code = 'AS' AND cause = 'flood' AND week_start BETWEEN DATE '2026-09-21' AND DATE '2026-09-28'
GROUP BY district
ORDER BY flood_claims DESC
LIMIT 200

SELECT SUM(claims) AS claims, ROUND(SUM(claim_amount)) AS claim_amount
FROM srv_property_claims_weekly
WHERE state_code = 'AS' AND cause = 'flood' AND week_start >= DATE '2026-09-21'
LIMIT 1

SELECT state_name, ROUND(SUM(sum_insured)) AS sum_insured
FROM srv_property_exposure_district
WHERE property_type = 'factory'
GROUP BY state_name
ORDER BY sum_insured DESC
LIMIT 5

SELECT cause, SUM(claims) AS claims
FROM srv_property_claims_weekly
WHERE week_start BETWEEN DATE '2025-10-06' AND DATE '2026-09-28'
GROUP BY cause
ORDER BY claims DESC
LIMIT 200

SELECT line, ROUND(SUM(premium)) AS premium
FROM srv_property_premium_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY line
ORDER BY premium DESC
LIMIT 200

SELECT insurer_code, ROUND(SUM(premium)) AS premium
FROM srv_property_premium_monthly
WHERE line = 'fire' AND month >= DATE '2026-01-01'
GROUP BY insurer_code
ORDER BY premium DESC
LIMIT 5

SELECT property_type, SUM(policies_written) AS policies_written, ROUND(SUM(premium) / SUM(policies_written)) AS avg_premium
FROM srv_property_premium_monthly
WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01'
GROUP BY property_type
ORDER BY avg_premium DESC
LIMIT 200

WITH c AS (SELECT line, SUM(claim_amount) AS claim_amount FROM srv_property_claims_weekly
           WHERE week_start BETWEEN DATE '2025-10-06' AND DATE '2026-09-28' GROUP BY line),
     p AS (SELECT line, SUM(premium) AS premium FROM srv_property_premium_monthly
           WHERE month BETWEEN DATE '2025-10-01' AND DATE '2026-09-01' GROUP BY line)
SELECT p.line, ROUND(c.claim_amount * 100.0 / p.premium, 1) AS loss_ratio_pct
FROM p JOIN c USING (line)
ORDER BY loss_ratio_pct DESC
LIMIT 200

SELECT property_type, SUM(policies_in_force) AS policies_in_force
FROM srv_property_exposure_district
WHERE district_code = 'MH-MUM'
GROUP BY property_type
ORDER BY policies_in_force DESC
LIMIT 200

SELECT week_start, ROUND(SUM(sum_insured)) AS sum_insured
FROM srv_property_exposure_weekly
WHERE week_start BETWEEN DATE '2026-07-13' AND DATE '2026-09-28'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT state_name, SUM(claims) AS claims
FROM srv_property_claims_weekly
WHERE cause = 'cyclone' AND week_start BETWEEN DATE '2025-10-06' AND DATE '2026-09-28'
GROUP BY state_name
ORDER BY claims DESC
LIMIT 200

SELECT cell_lat, cell_lon, SUM(policies_in_force) AS policies_in_force, ROUND(SUM(sum_insured)) AS sum_insured
FROM srv_property_exposure_grid
GROUP BY cell_lat, cell_lon
ORDER BY sum_insured DESC
LIMIT 10

SELECT week_start, ROUND(SUM(new_sum_insured)) AS new_sum_insured
FROM srv_property_exposure_weekly
WHERE state_code = 'MH' AND week_start BETWEEN DATE '2026-08-10' AND DATE '2026-09-28'
GROUP BY week_start
ORDER BY week_start
LIMIT 200

SELECT ROUND(SUM(sum_insured) FILTER (WHERE state_code = 'MH')) AS maharashtra_sum_insured,
       ROUND(SUM(sum_insured)) AS sum_insured,
       ROUND(SUM(sum_insured) FILTER (WHERE state_code = 'MH') * 100.0 / SUM(sum_insured), 1) AS share_pct
FROM srv_property_exposure_district
LIMIT 1

SELECT cause, SUM(claims) AS claims, ROUND(SUM(claim_amount)) AS claim_amount
FROM srv_property_claims_weekly
WHERE line = 'marine_cargo' AND week_start BETWEEN DATE '2025-10-06' AND DATE '2026-09-28'
GROUP BY cause
ORDER BY claims DESC
LIMIT 200

SELECT district, ROUND(SUM(sum_insured)) AS sum_insured
FROM srv_property_exposure_district
WHERE state_code = 'AS'
GROUP BY district
ORDER BY sum_insured DESC
LIMIT 5