Every query, and what it produced

Provenance for the numbers in this analysis. Three committed extracts produce the three output CSVs; a set of one-off verification queries is reproduced in full below, because several of them changed a conclusion and none is otherwise recoverable.

What this document does not cover, stated up front. A handful of published figures were computed ad hoc during review and their queries were not retained. They are listed in the closing section and should be treated as unreproducible until re-derived.

Assumptions behind these choices are numbered in ASSUMPTIONS.md.

The committed pipeline

Everything published is derived from exactly five CSVs. Rebuild in this order:

bq query --use_legacy_sql=false --format=csv --max_rows=100000 \
  < extract.sql            > output/ladder.csv
bq query --use_legacy_sql=false --format=csv --max_rows=100000 \
  < extract_lifecycle.sql  > output/lifecycle.csv
bq query --use_legacy_sql=false --format=csv --max_rows=100000 \
  < extract_rung_detail.sql > output/rung_detail.csv
bq query --use_legacy_sql=false --format=csv --max_rows=100000 \
  < extract_premium.sql    > output/premium_jobs.csv
bq query --use_legacy_sql=false --format=csv --max_rows=100000 \
  < extract_charts.sql     > output/charts.csv

--max_rows=100000 is load-bearing, not boilerplate. bq defaults to 100 rows and truncates without a warning. Run without it, extract_premium.sql returns 100 of its 137 rows, and because the result is ordered by rung the loss lands entirely on one rung - 35 occupations and 7.5% of the people, in a table that still looks complete and still sums to a plausible-looking number.

Extract	Output	Rows	What it feeds
`extract.sql`	`ladder.csv`	11 activity rows + 77 wage rows	The ladder table, every rung’s activity split, every wage-by-tenure table, the full ₹5,000–₹100,000 threshold grid
`extract_lifecycle.sql`	`lifecycle.csv`	33	The “Across a working life” table in the README and in every rung note
`extract_rung_detail.sql`	`rung_detail.csv`	592	Gender split, urban/rural, occupation mix, job function — every cut now carries a `population` column beside its share
`extract_premium.sql`	`premium_jobs.csv`	65	“What the premium jobs actually are” on rungs 10 and 11
`extract_charts.sql`	`charts.csv`	4 series	The four figures in `../EDUCATION-FINDINGS.md`

They share one base filter (the lifecycle extract widens the age band to 17–42 for its three life stages; everything else is identical):

FROM `avantifellows.external_data_sources.plfs_fact_persons`
WHERE release_id = 'calendar_2025'
  AND visit = 'V1'
  AND age BETWEEN 21 AND 34          -- lifecycle extract uses 17-42, see below
  AND weight_annual IS NOT NULL

and one rung definition, also identical in all three:

CASE
  WHEN gedu_lvl IN ('01','02','03','04','05','06') THEN '01. Below middle school'
  WHEN gedu_lvl = '07'                             THEN '02. Middle school'
  WHEN gedu_lvl = '08'                             THEN '03. Secondary (10th)'
  WHEN gedu_lvl = '10'                             THEN '04. Higher secondary (12th)'
  WHEN gedu_lvl = '11' AND tedu_lvl IN ('07','08','09','10')
                                                   THEN '05. Diploma, technical'
  WHEN gedu_lvl = '11'                             THEN '06. Diploma, non-technical'
  WHEN gedu_lvl = '12' AND tedu_lvl = '03'         THEN '07. Degree, engineering'
  WHEN gedu_lvl = '12' AND tedu_lvl = '04'         THEN '08. Degree, medical'
  WHEN gedu_lvl = '12' AND tedu_lvl IN ('02','05','06')
                                                   THEN '09. Degree, other technical'
  WHEN gedu_lvl = '12'                             THEN '10. Degree, general'
  WHEN gedu_lvl = '13' AND tedu_lvl = '03'         THEN '13. Postgraduate, engineering'
  WHEN gedu_lvl = '13' AND tedu_lvl = '04'         THEN '14. Postgraduate, medical'
  WHEN gedu_lvl = '13' AND tedu_lvl IN ('02','05','06')
                                                   THEN '12. Postgraduate, other technical'
  WHEN gedu_lvl = '13'                             THEN '11. Postgraduate, general'
END

Verified exhaustive and non-overlapping: the fourteen buckets recover exactly 263,446 people and 265,176,848 weighted — the complete 21–34 base. gedu_lvl='09' does not exist in this release.

And one definition of formal work, identical in all three:

IF(pas = '31' AND ern_reg > 0
   AND (job_pas IN ('2','3','4') OR ssec_pas NOT IN ('8','9')), 1, 0)

Verification queries that changed a conclusion

These were run once and are not part of the pipeline. V1 is reproduced verbatim and runs as written; V2–V7 use <base> and <rung CASE> placeholders standing for the blocks above and need those substituted before running. All have been re-run in that form and reproduce the stated results.

Why run. A reviewer noticed every occupation table summed to exactly 100.00 within its rung, which cannot happen when small cells are suppressed.

WITH b AS (
  SELECT ocu_pas, weight_annual,
    CASE WHEN gedu_lvl='12' AND tedu_lvl='03' THEN '07 engineering'
         WHEN gedu_lvl='12' AND tedu_lvl='04' THEN '08 medical'
         WHEN gedu_lvl='12' AND tedu_lvl IN ('02','05','06') THEN '09 other technical' END rung,
    IF(pas='31' AND ern_reg>0
       AND (job_pas IN ('2','3','4') OR ssec_pas NOT IN ('8','9')),1,0) f
  FROM `avantifellows.external_data_sources.plfs_fact_persons`
  WHERE release_id='calendar_2025' AND visit='V1' AND age BETWEEN 21 AND 34
    AND weight_annual IS NOT NULL
),
g AS (SELECT rung, ocu_pas, COUNT(*) n, SUM(weight_annual) w FROM b
      WHERE f=1 AND ocu_pas IS NOT NULL AND rung IS NOT NULL GROUP BY 1,2)
SELECT rung, ocu_pas, n,
  ROUND(100*w/SUM(w) OVER (PARTITION BY rung),2) share_TRUE,
  ROUND(100*w/SUM(IF(n>=15,w,0)) OVER (PARTITION BY rung),2) share_PUBLISHED,
  ROUND(100*SUM(IF(n>=15,w,0)) OVER (PARTITION BY rung)
        /SUM(w) OVER (PARTITION BY rung),1) pct_weight_kept
FROM g QUALIFY ROW_NUMBER() OVER (PARTITION BY rung ORDER BY w DESC) <= 3

Result. BigQuery evaluates HAVING before analytic functions, so the original extract’s denominator counted only surviving occupations.

	Published	True	Weight kept
Engineering, software developers	50.60%	45.76%	90.4%
Medical, doctors	33.82%	24.16%	71.4%
Other technical, primary teachers	30.31%	15.77%	52.1%

Action. extract_rung_detail.sql rewritten to aggregate before filtering. Every occupation table in every rung note corrected; rung 09’s “the B.Ed rung” framing withdrawn.

V2. Cluster-robust standard errors on the formal rate

Why run. The ladder reported no uncertainty anywhere, and three rungs sat within 3 points of one another.

WITH b AS (
  SELECT CONCAT(st,dc,strm,sstrm,IFNULL(ss,''),mfsu) fsu, weight_annual, <rung CASE> rung,
    IF(pas='31' AND ern_reg>0
       AND (job_pas IN ('2','3','4') OR ssec_pas NOT IN ('8','9')),1,0) f
  FROM `avantifellows.external_data_sources.plfs_fact_persons`
  WHERE release_id='calendar_2025' AND visit='V1' AND age BETWEEN 21 AND 34
    AND weight_annual IS NOT NULL),
p AS (SELECT rung, SUM(f*weight_annual)/SUM(weight_annual) phat,
             SUM(weight_annual) W, COUNT(DISTINCT fsu) m FROM b GROUP BY rung),
z AS (SELECT rung, fsu, SUM(weight_annual*(f-phat)) zc FROM b JOIN p USING(rung) GROUP BY 1,2)
SELECT rung, 100*phat rate, 100*SQRT(m/(m-1)*SUM(zc*zc))/W se_pp
FROM z JOIN p USING(rung) GROUP BY rung, phat, m, W

Result. Technical diploma 30.77 ± 1.21; medicine 32.41 ± 2.55; postgraduate 29.11 ± 0.71. Diploma vs PG: t = 1.18. PG vs medicine: t = 1.25. Diploma vs general degree: t = 11.7.

Action. Two claims retracted — “a postgraduate degree does not top the list” and “medicine is the only rung where women’s employment exceeds men’s” (t = 0.24). The diploma-beats-general-degree finding stands.

V3. Is rung 01’s flat wage growth an age artefact?

Why run. A 1.00× multiple on a 37-person cell invited the question.

SELECT CASE WHEN dur_pas IN ('1','2') THEN 'entry'
            WHEN dur_pas IN ('3','4') THEN 'mid' ELSE 'over3' END step,
  COUNT(*) n, ROUND(AVG(age),1) mean_age,
  ROUND(APPROX_QUANTILES(ern_reg,2)[OFFSET(1)]) med_all,
  COUNTIF(age<=26) n_young,
  ROUND(APPROX_QUANTILES(IF(age<=26,ern_reg,NULL),2)[OFFSET(1)]) med_21_26
FROM <base, gedu_lvl IN ('01'..'06'), formal=1, dur_pas IN ('1'..'5')>
GROUP BY step

Result. Mean ages 27.4 / 27.1 / 29.6 — the cells are not entrants versus veterans. Restricted to ages 21–26: ₹10,000 → ₹12,300, a 1.23× rise.

Action. The tenure-based version of “time in work buys nothing” withdrawn. The claim now rests on the lifecycle lens (₹12,200 → ₹12,000 → ₹12,000 on cells of thousands).

V4. Does the formal-work `OR` leak, and where?

SELECT <rung>,
  ROUND(100*SUM(IF(pas='31' AND ern_reg>0
       AND (job_pas IN ('2','3','4') OR ssec_pas NOT IN ('8','9')),weight_annual,0))
       /SUM(weight_annual),2) formal_current,
  ROUND(100*SUM(IF(pas='31' AND ern_reg>0 AND ssec_pas NOT IN ('8','9'),weight_annual,0))
       /SUM(weight_annual),2) formal_strict
FROM <base> GROUP BY rung

Result. Rung 01: 1.66% → 1.26%. Engineering: 52.58% → 51.20%. Top-to-bottom ratio 31.7× → 40.6×. Separately, contract-only admissions are 23.9% of rung 01’s formal count at a ₹10,000 median, below the ₹11,000 of those classed informal.

Action. Added as a sensitivity table. The headline is now stated as a 32–41× range.

V5. What the diploma rung actually contains

Why run. The first version of this analysis defined technical as tedu_lvl != '01'.

SELECT tedu_lvl, ANY_VALUE(tedu_label) label, COUNT(*) n,
  ROUND(SUM(weight_annual)/1e6,2) pop_mn,
  ROUND(100*SUM(IF(<formal>,weight_annual,0))/SUM(weight_annual),2) pct_formal
FROM <base> WHERE gedu_lvl='11' GROUP BY tedu_lvl ORDER BY n DESC

Result. Code 08 engineering (n=3,000, 33.41% formal) and code 11 other subjects (n=1,890, 25.39% formal) were being pooled as “technical”.

Action. Technical redefined as IN ('07','08','09','10'). The rung moved 29.07% → 30.77%.

V6. Do the merged graduate analysis’s diploma buckets contain diploma holders?

SELECT gedu_lvl, ANY_VALUE(gedu_label) label,
  COUNTIF(tedu_lvl='01') no_tech,
  COUNTIF(tedu_lvl IN ('02','03','04','05','06')) tech_degree,
  COUNTIF(tedu_lvl IN ('07','08','09','10','11')) dip_below_grad,
  COUNTIF(tedu_lvl IN ('12','13','14','15','16')) dip_grad_plus,
  COUNT(*) n
FROM <base, ages 21-34> GROUP BY gedu_lvl ORDER BY gedu_lvl

Result. gedu_lvl='12' holds 1,951 + 2,545 = 4,496 diploma-holding graduates — exactly the size of the companion’s two “diploma” buckets. The real diploma population, gedu_lvl='11', is 6,821 people / 6.91m, absent from that analysis entirely.

Action. Correction documented in this analysis’s README and in both diploma rung notes.

V7. Did the companion’s tenure lens apply its documented age bound?

SELECT MIN(age) min_age, MAX(age) max_age, COUNT(*) n,
  COUNTIF(age BETWEEN 21 AND 34) n_21_34, COUNTIF(age > 34) n_over_34,
  ROUND(APPROX_QUANTILES(ern_reg,2)[OFFSET(1)]) median_all,
  ROUND(APPROX_QUANTILES(IF(age BETWEEN 21 AND 34, ern_reg, NULL),2)[OFFSET(1)]) median_21_34
FROM <base> WHERE gedu_lvl='12' AND tedu_lvl='03' AND pas='31' AND ern_reg>0
  AND dur_pas='5' AND (job_pas IN ('2','3','4') OR ssec_pas NOT IN ('8','9'))

Result. Ages 22 to 70; 952 of 2,168 were over 34. Median ₹48,000 unbounded against ₹42,500 for 21–34.

Action. Age bound added to the tenure branch in the companion’s extract.sql. Engineering’s growth multiple moved 1.92× → 1.68×; all three companion documents updated.

V8. Does the premium-jobs long tail add up?

Why. The first run of the occupation tail was read straight into a summary: “all 37 occupations for the general-degree rung”. A cheap arithmetic check - does the share column sum to 100? - said 92.55%.

The check.

-- the same population, counted without any grouping or ordering
SELECT COUNTIF(ocu_pas IS NOT NULL) AS with_occ, COUNT(DISTINCT ocu_pas) AS distinct_codes
FROM `avantifellows.external_data_sources.plfs_fact_persons`
WHERE release_id='calendar_2025' AND visit='V1' AND age BETWEEN 21 AND 34
  AND weight_annual IS NOT NULL AND gedu_lvl='12'
  AND tedu_lvl NOT IN ('02','03','04','05','06')
  AND pas='31' AND ern_reg*12>=600000 AND (job_pas IN ('2','3','4') OR ssec_pas NOT IN ('8','9'))

Result. 727 people across 72 distinct occupation codes - not 650 across 37.

Cause. The query had been run without --max_rows, and bq caps output at 100 rows silently. The result was ordered by rung, so the two rungs did not share the loss evenly: the postgraduate rung sorted first and kept all 63 of its rows, and the undergraduate rung was cut at 37 - losing its 35 smallest occupations, 77 people, 7.5% of the rung. Every surviving row was correct. The table looked complete, was internally consistent, and understated the tail.

Action. Three changes, because the fix and the guard are not the same thing:

extract_premium.sql now emits an explicit residual row pooling every cell under 5 people, so the share column is forced to sum to 100 and truncation cannot hide.
check_notes.py verifies the cumulative column against a running sum of the shares - the check that would have caught this without anyone thinking to look.
The --max_rows requirement is stated in the extract header, in README.md and in the pipeline section above.

What it changed in the published numbers: nothing. The three-cluster shares (36.54% / 42.95%) come from the cluster block, which was never truncated. Only the tail table was wrong, and it was corrected before publication.

V9. Can the shares be turned into headcounts?

Why. Every rung note now prints a population beside every share. SUM(weight_annual) is a headcount estimate rather than a sample size, so the numbers exist — but the schema warns (gotcha 14) that the weights sum to PLFS’s own Census-2011-anchored projection. That warning is itself an estimate, so it was checked rather than repeated.

The checks. Three anchors, deliberately independent of one another:

-- 1. what the weights actually sum to, by age band
SELECT COUNT(*) AS n, ROUND(SUM(weight_annual)/1e6, 2) AS millions
FROM `avantifellows.external_data_sources.plfs_fact_persons`
WHERE release_id='calendar_2025' AND visit='V1' AND weight_annual IS NOT NULL

-- 2. the graduate-flow anchor, from AISHE rather than from memory.
--    gender='Total' IS REQUIRED - gender carries Male/Female/Total, so summing it doubles the
--    answer. Run without it and UG graduates read 15.51m instead of 7.754m, which is plausible
--    enough to survive a read-through. AISHE's own published anchor for 2021-22 is 7,754,223.
SELECT aishe_year, SUM(value)/1e6 AS ug_millions
FROM `avantifellows.external_data_sources.aishe_fact_higher_ed_students`
WHERE cut='state_level' AND metric='graduates' AND gender='Total'
  AND state!='All' AND level='Under Graduate'
GROUP BY 1 ORDER BY 1

Result.

Anchor	Observed	Expected	Implied correction
Total weighted population	1,193m	~1,450m	1.22×
~2.5 crore per single-year cohort, 21–34	265.2m	~350m	1.32×
AISHE UG graduate flow vs PLFS degree stock	71.6m	~84m	1.17×

The three bracket the correction at 1.17×–1.32×, and it is not uniform by age, so a single global scalar would be wrong.

Action. Population counts are published as lower bounds with that range stated once, in ASSUMPTIONS.md A4 — not silently scaled. Doing it properly needs MoSPI’s Population Projections for India and States 2011–2036 loaded as a documented table; raised with the data team. No share, rate or comparison anywhere in the corpus is affected, because numerator and denominator carry the same deflation.

Reproducing a single rung

Every rung note’s numbers come from filtering the three CSVs on that rung. For example, rung 05:

import pandas as pd
L = pd.read_csv('output/ladder.csv')
R = '05. Diploma, technical'
L[(L.rung == R) & (L.section == 'activity')]         # the activity split
L[(L.rung == R) & (L.section == 'wage_by_tenure')]   # the wage table
pd.read_csv('output/lifecycle.csv').query('rung == @R')    # entry / 30 / 40
pd.read_csv('output/rung_detail.csv').query('rung == @R')  # gender, sector, occupation

Every number in the ladder table, the wage tables, the lifecycle table, the gender and sector splits and the occupation tables comes from those three files and nowhere else.

Figures with no retained query

These were computed during review, are published, and cannot currently be reproduced from anything in this repository. Each should be re-derived before being quoted, or dropped.

Figure	Where
Cluster-robust SEs by rung × gender (medicine ±3.30 / ±3.74, t = 0.24)	`rungs/08`, `README.md`
Engineering p45/p55 by sex (₹35,000 / ₹40,000) and weighted means (₹45,350 / ₹46,704)	`README.md`, `rungs/07`
Design effect of 1.14× for engineering aged 22–24	`ASSUMPTIONS.md` F1
Engineering unemployment on a labour-force base (~19%)	`FINDINGS.md` D4

V2 below computes cluster-robust SEs by rung only; it does not produce the gender-split figures. That gap is why the medicine gender claim is graded a retraction (R2) rather than a finding.