Debugging a health app's SQL before the board meeting

01The brief

Health Co's General Manager of Analytics needed figures on the active user base for a board meeting. Their team's SQL script against the health.user_logs table wouldn't run. My job was to debug it, run it, and answer nine questions:

02The data

One table, one row per measurement a user logs in the app. Three measure types share the table, so the blood pressure columns are only meaningful on blood pressure rows.

03Approach

1. DebugRead the script against the real schema, then fix syntax, wrong columns, wrong values and wrong logic, one query at a time.
2. RunExecute the fixed script read-only against the database and save the raw output as the record of the result.
3. Cross-checkProve each answer with a second, independent query before it goes in front of a board.

Questions 2 to 8 all depend on one per-user summary, so the script builds it once as a temporary table:

CREATE TEMP TABLE user_measure_count AS
SELECT
  id,
  COUNT(*)                AS measure_count,
  COUNT(DISTINCT measure) AS unique_measures
FROM health.user_logs
GROUP BY 1;

04The bugs that mattered

Most of the 16 bugs were syntax and typos. Four would have produced wrong numbers, or no numbers, in front of the board.

The median was taken over user IDs

PERCENTILE_CONTINUOUS(0.5)
  WITHIN GROUP (ORDER BY id)

PERCENTILE_CONT(0.5)
  WITHIN GROUP (ORDER BY measure_count)

Besides the wrong function name, it ordered by the user ID, which says nothing about activity. The median has to be over the per-user counts.

A filter on a value that doesn't exist

WHERE measure is 'blood_sugar'

WHERE measure = 'blood_glucose'

IS is for NULL and true/false checks. Even with =, 'blood_sugar' matches no rows, so the board would have seen zero glucose users.

Summing IDs to count users

SELECT SUM(id)

SELECT COUNT(*)

The question is how many users, and the IDs are text hashes. Counting rows in the one-row-per-user table answers it.

An aggregate inside WHERE

WHERE COUNT(DISTINCT measures) >= 2

WHERE unique_measures >= 2

WHERE filters rows before anything is counted, so it can't hold an aggregate. The temp table already stores the count, so the fix is to filter on that column.

05Results

Where the activity really is

The mean says the average user logs 79 times. The median says the typical user logs twice. Grouping users by how often they log shows why:

06How I know the numbers are right

A fixed script that runs is not the same as a correct one. Each answer was checked by a different route:

A data trap worth flagging

On rows that aren't blood pressure, systolic and diastolic hold a mix of 0 and empty values. Glucose rows alone have 13,111 zeros. Aggregates skip empty values but count zeros, so without the measure = 'blood_pressure' filter the median blood pressure comes out as 0 / 0. One blood pressure row also has a systolic of 0. It doesn't move the median, but it belongs in a data-quality note.

07What I'd tell the board

• Report the median next to the mean. "79 measurements per user" describes almost nobody. The typical user logs twice.
• Engagement is concentrated. 5 users (0.9%) produce 62.4% of all logs, so any average-based KPI mostly tracks those five people.
• Retention is the open question. 46.8% of users logged only once, and only 9% use all three measurement types.
• Fix the data at the source. Use empty values, not zeros, for readings that don't apply, so a missing filter can't quietly produce a median of 0.

08The final script

-- 1. Unique users
SELECT COUNT(DISTINCT id) AS unique_users
FROM health.user_logs;

-- Per-user summary for questions 2-8
DROP TABLE IF EXISTS user_measure_count;
CREATE TEMP TABLE user_measure_count AS
SELECT
  id,
  COUNT(*) AS measure_count,
  COUNT(DISTINCT measure) AS unique_measures
FROM health.user_logs
GROUP BY 1;

-- 2. Mean measurements per user
SELECT ROUND(AVG(measure_count), 2) FROM user_measure_count;

-- 3. Median measurements per user
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY measure_count)
FROM user_measure_count;

-- 4. Users with 3 or more measurements
SELECT COUNT(*) FROM user_measure_count WHERE measure_count >= 3;

-- 5. Users with 1,000 or more measurements
SELECT COUNT(*) FROM user_measure_count WHERE measure_count >= 1000;

-- 6. Users who logged blood glucose
SELECT COUNT(DISTINCT id) FROM health.user_logs
WHERE measure = 'blood_glucose';

-- 7. Users with at least 2 measure types
SELECT COUNT(*) FROM user_measure_count WHERE unique_measures >= 2;

-- 8. Users with all 3 measure types
SELECT COUNT(*) FROM user_measure_count WHERE unique_measures = 3;

-- 9. Median systolic / diastolic
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY systolic)  AS median_systolic,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY diastolic) AS median_diastolic
FROM health.user_logs
WHERE measure = 'blood_pressure';