Data, SQL and Getting to the Answer

Sunita Deshpande has run Vidyankur for nine years. The centre’s entire record is one spreadsheet named fees_master_FINAL_v4.xlsx: one row per payment, with the student’s name, parent’s phone number, class, batch, month, amount and mode. It has 41,000 rows. It takes fifty seconds to open, and for nine years it has been good enough.

In January three things happen in the same fortnight.

A parent calls to say she has been paying for her son since June and the centre has just sent her a defaulter notice. She is right. Her phone number changed in August; the office updated it in the rows they could find, and missed eleven. The defaulter list is built by matching on phone number, so those eleven payments now belong to nobody.

The second is quieter. Sunita asks her office manager, Faiz, how many students are in Class 10 Maths. He gives her 214. Her senior teacher gives her 227 from the same file an hour later. Neither number is wrong: Faiz counted rows where batch is Class 10 Maths, and the teacher counted rows where batch starts with Class 10, which also catches Class 10 Maths (Evening), a batch someone created in July by typing a new value into the column. Nothing in the file says which of the two is the real batch list, because there is no batch list. There are only whatever strings people have typed.

The third is that Faiz, tidying up, sorts the sheet by amount to find the largest payments and does not extend the selection to the whole row. Two thousand rows now have somebody else’s amount against their name. He notices within a minute and undoes it. Nobody can prove the undo was complete.

Sunita’s nephew, who is finishing a diploma, offers to rebuild the whole thing as a small Postgres database: a students table with a generated id and the phone as a plain column, a batches table with one row per batch, an enrolments table in the middle because a student can be in several batches, and a payments table whose student_id and batch_id point at rows that must exist. He estimates eleven working days, most of it spent deciding what the existing rows mean rather than typing SQL.

Eleven working days lands squarely on the fee drive, which is the fortnight that pays for the year. During the rebuild there is no defaulter list, because the old sheet is frozen and the new tables are not loaded. Faiz cannot write SQL. Sunita cannot write SQL. If the nephew goes back to college in March, the centre owns a database it cannot query, having given up a spreadsheet it could at least look at.

Against that: the fee drive is the exact moment when the defaulter list has to be right. A notice sent to a parent who has paid costs a student. Last year the centre lost four that way, which is about a hundred and forty thousand rupees a year in fees, and Sunita only knows about the four who complained.

There is a third option nobody has costed. Keep the spreadsheet as the working record for this year, and spend two days building only the parts that stop the specific bleeding: a real list of batches, a real list of students with a permanent id written back into the sheet, and a nightly export into a database that answers the two questions (who owes what, and who is in which batch) while all writing continues in the sheet as before.

That option leaves the sheet’s other faults untouched for a year. It also means the same fact lives in two places for a year, which is the problem the rebuild exists to remove.

The programme runs 46 sub-centres. Its database has three tables that matter tonight. visits holds one row per patient visit: 214,000 rows for the year. dispensations holds one row per medicine handed out during a visit, with a quantity and a unit cost: 611,000 rows. referrals holds one row per onward referral made during a visit: 38,000 rows.

The funder wants one page: for each sub-centre, the number of visits, the total drug cost, and the number of referrals. Priya, the programme’s data officer, writes what the question asks for.

SELECT v.centre_id,
       count(*)                         AS visits,
       sum(d.qty * d.unit_cost)         AS drug_cost,
       count(r.id)                      AS referrals
FROM visits v
JOIN dispensations d ON d.visit_id = v.id
LEFT JOIN referrals   r ON r.visit_id = v.id
GROUP BY v.centre_id;

The total drug cost comes back as 1,84,00,000 rupees. She knows, without checking anything, that the programme’s entire drug budget for the year was 1,72,00,000 and that it underspent. The number is twelve lakh above a ceiling it cannot have crossed.

She checks the row count. visits has 214,000 rows; the joined result has 638,000. Something on the right matched more than once, which she expects: a visit has several dispensations. But the drug cost should still be right, because each dispensation appears once per visit. It is the referrals join that is doing the damage in the other direction, and the drug cost that is inflated by it: a visit with four dispensations and two referrals emits eight rows, so each of the four dispensation rows is counted twice.

It is 8pm. Priya has three options and each costs something.

The first is to pre-aggregate. Two CTEs, one summing dispensations per visit and one counting referrals per visit, then join both summaries to visits one row to one row. It is the correct answer, it is about fifteen minutes of typing, and it needs to be checked afterwards, which is another half hour she has.

The second is faster and is what a colleague suggests on the phone: add DISTINCT. count(DISTINCT r.id) for referrals and count(DISTINCT v.id) for visits both become right immediately. The drug cost does not, because there is no sum(DISTINCT) that means what she needs: sum(DISTINCT d.qty * d.unit_cost) would collapse two genuinely separate dispensations of the same medicine at the same price into one, and would under-report instead of over-reporting. The colleague’s fix repairs the two numbers she can see are wrong and quietly corrupts the one she cannot.

The third is to send last year’s methodology note with a figure computed the way it was computed last year, which she knows was the same query, and flag the discrepancy in a footnote. That costs nothing tonight. It also means that if last year’s report used this query, last year’s drug cost was inflated the same way, and the funder has been told a wrong number twice.

The funder’s officer has already said, in an email Priya has open, that a resubmission after the deadline moves the district to the next quarterly cycle, which delays a tranche of about forty lakh by three months.

The operator’s contract with the city contains one performance number: average waiting time at a stop, which must stay under nine minutes. Every quarter, Karthik’s team reports it. Every quarter it comes in between seven and eight minutes, and every quarter the committee’s public consultation produces a stack of complaints about waiting half an hour at particular stops on particular evenings.

The number is computed from arrivals, one row per bus per stop per arrival, with a timestamp. Waiting time is derived as the gap since the previous bus at that stop. There are 4.1 million rows a quarter.

This quarter Karthik is asked by a new committee member for the median as well, and for the 90th percentile. He runs them. The mean is 7.8 minutes. The median is 5.2. The 90th percentile is 19 minutes. The 99th is 54.

Then he looks at how the mean has been computed for the last three years, and finds two things.

The first is that the quarterly figure has always been the average of the daily figures held in a daily_stats table, which stores one row per stop per day with an avg_wait column. Sunday has a tenth of the traffic of a Tuesday and much longer gaps, so its long waits sit in the average with the same weight as a weekday’s short ones, but the far bigger effect runs the other way, because a stop with four buses a day and one with two hundred contribute equally to an average of averages. Recomputing from the raw gaps, weighted properly, gives a mean of 11.4 minutes, not 7.8.

The second is the denominator. The gap is only recorded when two consecutive arrivals are logged at the same stop. When a bus is cancelled, no arrival is logged, so the long wait it caused is not a row in the table at all. The rows that exist are the rows where a bus turned up. The measure is a measure of waiting time among people whose bus came.

Karthik has two weeks and a decision that is not really technical.

He can report the number the way it has always been reported, with the median and the percentiles added as supplementary information, and note the weighting question in an annexe. That keeps the contract compliant, keeps three years of published figures internally consistent, and lets the committee see the tail for the first time. It also means the headline number in the minutes will be 7.8 minutes, and the headline number is what the newspaper prints.

Or he can report 11.4 minutes as the mean, with the method change explained. That breaches the contractual ceiling for the first time in three years, triggers a review clause, and (because the previous figures cannot be recomputed for stops where the arrival data has been archived) produces a series with a step in it that no one can smooth. The operator’s managing director has already asked him, not unreasonably, whether a number that changes because the analyst changed his mind is a number at all.

The cancellation problem breaks both options equally, and fixing it needs the scheduled timetable joined against the arrivals (a spine of expected buses, LEFT JOINed to what actually arrived) which is three days of work he does not have this quarter.

Ananya runs analytics for the app. Every month she produces a cohort grid: learners grouped by the month of their first completed lesson, and for each cohort the share still active one, two, three and more months later. Active means completing at least one lesson in the month.

The grid for the last fourteen months, pivoted the way the board likes it, shows month-6 retention of 31% for the January cohort of last year, 29% for March, 26% for June, 22% for September and 14% for November. Read across that row and the story is unmistakable: retention has almost halved in a year. The board’s paper, drafted by someone else from her grid, says the content is stale and recommends the forty-lakh investment in new material.

Ananya checks the numbers and they are all arithmetically correct. Then she checks the shape of the grid, and finds that the November cohort is four months old. Its month-6 cell is not 14%; it is empty. What the pivot has produced for November is the last cell that had any data in it, which is month 4, and the board paper has read a triangle along an edge that does not exist.

The genuinely comparable comparison is a column, not a row: month-3 retention for every cohort that has reached month 3. That series is 34%, 33%, 31%, 32%, 30%, 29%: a slow decline of about five points over a year, not a halving.

That is still a decline, and now Ananya has a second problem. Two of the cohorts contain a promotion. In June and July the app ran a free-trial campaign with a telecom partner that brought in 21,000 registrations, of whom a large share completed one lesson and never returned. Those learners are in the June and July cohorts by the definition being used (first completed lesson) and they drag those cohorts’ curves down without anything having changed about the content. Excluding them is defensible and is also the kind of exclusion that can be made to produce any number you like.

She has three days, and the decision is what to put in front of the board.

If she sends the corrected column-wise reading, the recommendation loses most of its evidence. The head of content, who has spent two months preparing the plan, will point out, correctly, that a five-point decline is still a decline and that the app’s competitors ship new material monthly. Killing the plan on Ananya’s chart and then losing ground would be an expensive way to be right about a pivot table.

If she says nothing and lets the paper go, the board approves forty lakh on the strength of a number that means the November cohort is young. Ananya will know. And the same grid goes to the board every quarter, so the error will be repeated, will eventually be found by somebody, and will make every future number she produces arguable.

There is a third path with its own cost: present both readings and the promotion effect, and ask the board to decide on a smaller pilot. That is honest, it delays the content plan by a quarter in a market where a quarter matters, and it hands a room of eleven people a methodological argument three days before they were expecting a recommendation.

The partner hospital pays Sahyog a fee per referred test. Once a year the hospital sends a CSV and the two finance teams reconcile it against Sahyog’s own records. This year’s file has 28,900 rows and one column that will not behave: referred_on, which arrives as text like 14/01/2026, 2026-01-14, 14-Jan-26 and, in a few hundred rows, 45671.

Meena, who does the analysis, stages the file exactly as she was taught: every column as text, into a raw_referrals table that is never updated, with a load_file column recording where each row came from. Then she writes the casting view and counts what refuses.

to_date(referred_on, 'DD/MM/YYYY') parses 27,720 rows and fails on 1,180. She lists the failures with their frequencies. Six hundred and ten are 14-Jan-26 style, three hundred and twelve are ISO, two hundred and fifty-eight are five-digit numbers (spreadsheet serials, days since 30 December 1899) and none of the three is difficult to parse once identified. It is an hour’s work to write a CASE that tests the shape of each value and applies the right parser.

The reconciliation is due to the partner’s finance office in two days, and Meena’s manager points out that 1,180 out of 28,900 is 4.1%, that the fee is a flat 340 rupees per referral, and that the difference is about four lakh on a settlement of just under a crore. His proposal is to load the 27,720 rows that parse, settle on those, and reconcile the remainder next quarter when there is time. Four lakh is real money and it is also within the tolerance the two organisations have used before.

Meena runs one more query before answering: the 1,180 failing rows grouped by centre and by month.

They are not spread across the year and the nine centres. One thousand and four of them are from the Pollachi centre, and every one of those is from 14 January onwards. Pollachi changed its practice-management software in the second week of January, and the new software writes dates in a different format and exports spreadsheet serials for anything entered on a mobile device.

So the 4.1% is not a random 4.1%. It is most of one centre’s second half of the year. Dropping it does not lose four lakh evenly; it removes Pollachi from the analysis from mid-January, and Pollachi is the centre whose referral volume the partner hospital has been questioning. A settlement computed on the parsing rows would under-pay Sahyog for that centre specifically, and, worse for the relationship, would produce a referral count for Pollachi that supports the partner’s argument that the centre is not referring.

Meena’s manager is not being careless. His point is that an hour of parsing work invites a second hour of checking, and a third when the parsed dates turn out to move rows between months, and that the deadline is real: the partner’s own quarter closes on Friday and a late reconciliation waits three months for the next cycle. He has also seen a colleague spend a week on a date column and arrive at the same settlement figure, and he is weighing a certain cost in staff time against an uncertain gain of a few lakh. What he has not seen is Meena’s query grouping the failures by centre, because she ran it after he made his proposal and before she answered him.

Results go live at nine the next morning. At six in the evening the exam controller discovers that one paper (Applied Mathematics II, taken by 4,100 students) has been entered against the wrong scheme. The paper was moderated with a 5-mark grace applied to anyone who scored between 35 and 39, and the grace was applied twice by two different clerks working from the same list, so those students carry marks 5 higher than they should.

Ravi, who maintains the college’s ERP database, has the marks table in front of him: one row per student per paper, with marks_obtained, entered_by and updated_at. He also has a moderation_log table listing which students received grace and by whom, with 1,180 rows for this paper, of which some students appear twice.

The correction is arithmetically simple: subtract 5 from the students whose grace was applied twice. Identifying them is a group and a count. The statement writes itself:

UPDATE marks m
SET    marks_obtained = m.marks_obtained - 5
FROM   moderation_log l
WHERE  l.student_id = m.student_id
  AND  l.paper_code = m.paper_code
  AND  m.paper_code = 'AM-II';

Ravi does not run it, for the reason the module gives: an UPDATE ... FROM whose right-hand side matches more than once per target row updates that row with one of the matches and does not say which. Here the right-hand side matches twice for exactly the students he is trying to fix (that duplication is the bug), and Postgres will apply the subtraction once per target row, not once per match. The statement would take 5 off every student with any grace at all, including the 1,180 minus the duplicates who received it correctly. The rehearsal query he runs instead, counting matches per target row, returns 612 rows with a count above one. That is the population.

Which leaves the decision, at half past six.

The first option is a corrected statement: subtract 5 only from the 612 students identified by the group-and-count, inside a transaction, with the row count checked against 612 before committing. Twenty minutes including the rehearsal. It requires Ravi to be confident that moderation_log is a complete record of what the clerks did, and the clerks were working from a printed list, entering marks directly into the ERP screen, with the log written by a trigger that was added eight months ago and has never been audited.

The second is what the exam controller wants: hold publication, re-enter the paper from the moderated answer sheets tomorrow, and publish at five in the evening. That is unarguably correct and it costs a day in which 6,200 students, most of whom need the result for internship applications closing that week, refresh a page that says nothing. The college did this two years ago and it made the local newspaper.

The third is to publish on time with the paper’s results withheld, and issue that one paper later. It affects only the affected students, and it also tells 4,100 of them, publicly, that something went wrong with their marks, before anyone knows whether the fix is right.

What none of the three options addresses is that the ERP’s application user owns the schema and can alter it, that every clerk shares one login, and that updated_at is set by the application rather than by the database, so a row changed directly in the database tonight will carry a timestamp that says nothing about who changed it.