Guided Projects / Explain Your Query — HealSync: Claims, Telemedicine, Surveys, Workforce

🧭 PROJECT BRIEF

Explain Your Query — HealSync: Claims, Telemedicine, Surveys, Workforce

Intermediate SQL Interview Practice 🕒 ~20 hours 📦 0+ published takes

You have been given six tables from a hospital network, HealSync: insurance claims, telemedicine sessions, patient surveys, public-health measurements, research studies and the workforce. An interviewer wants two things from you: the answers, and the reasoning said aloud.

Work through the seventeen tasks with the book Explain Your Query beside you: each task follows one chapter, and each chapter ends with a Try It box that describes the same task. Read the tables before you query them, test the keys, and find the rows recorded twice and the sessions whose doctor does not exist. Answer the questions interviewers ask on these tables: inner against left join, WHERE against HAVING, the forms of COUNT, UNION against UNION ALL, CTE against subquery, ROW_NUMBER against RANK, and why a query is slow. Then do the take-home on the reimbursement rate, deliver two lists as you would at work, and run the ten checks before trusting a join.

The project is one notebook for the whole book. Add each chapter's blocks below the previous chapter's, in order, and assign them to that chapter's task. Later tasks read views built by earlier ones (reimbursement_by_coverage from Task 5, claims_clean and sessions_clean from Task 8, patient_events and patient_sequence from Task 12), so keep the blocks that create a view above the blocks that use it. Where a task asks you to save a number as a view named like ch01_answer, its automatic check reads that view. Views you create are gone after a page reload until you press Run all, so after reopening the project press Run all before running anything else.

You need basic SQL (SELECT, WHERE). Your notebook is the deliverable: assign every block to its task, write the notes each task asks for, run the automatic checks, press Run all until it passes from top to bottom, and submit.

🧪 Runs in your browser

Dataset → Profile → Clean → SQL → Chart → Quality → Python → Output

Real SQL (DuckDB) and real Python (pandas) run entirely in your browser — nothing to install, and your data never leaves it. ▶▶ Run all replays the whole notebook top-to-bottom; a clean pass marks the published repo as reproducible ⛓.

☑️ The tasks

Ask HealSync three questions

Chapter 1. In SQL blocks, run the chapter's queries, including the three from the section Three Questions Asked of HealSync. Then write one query that returns the number of rows in all six tables together, and save it as a view named ch01_answer with one column, total_rows. In a note, write one sentence for each of the three questions saying what it returned, and for the second, the question you would send to the owner of the claims data. Assign the blocks to Task 1.

Meet the six tables

Chapter 2. Run the chapter's queries, and in a note write the grain sentence and the key of each of the six tables, as in Table 2-1. Save the number of distinct patients in insurance_claims as a view named ch02_answer with one column, claim_patients. Then run the five first-look queries on telemedicine_usage (rows against distinct Usage_ID, SUMMARIZE, the values of Purpose, missing values, and the sessions whose Doctor_ID has no partner in healthcare_workforce) and write five lines of findings in a note. Assign the blocks to Task 2.

Count a quarter three ways

Chapter 3. Run the chapter's queries. Count the claims of the fourth quarter of 2023 three ways: with a half-open range (Claim_Date on or after DATE '2023-10-01' and before DATE '2024-01-01', which can be written DATE '2024-01-01' > Claim_Date), with EXTRACT(quarter FROM Claim_Date) = 4, and with strftime. Save the half-open count as a view named ch03_answer with one column, q4_claims. In a note, write the range as it would run in SQL Server and say which form you would use in an interview and why. Assign the blocks to Task 3.

Read a query plan

Chapter 4. Run the chapter's queries. The Workbench loads the six tables as views over the files, so for the storage query first run CREATE OR REPLACE TEMP TABLE ch04_claims AS SELECT * FROM insurance_claims and read pragma_storage_info('ch04_claims'). Run EXPLAIN and EXPLAIN ANALYZE on the join of approved claims to sessions, and in a note give each operator's estimated and actual rows and say what column pruning and filter pushdown did. Then save the join's row count, run without EXPLAIN, as a view named ch04_answer with one column, joined_rows. Assign the blocks to Task 4.

Solve the one-to-many problem

Chapter 5. Run the chapter's queries, including CREATE OR REPLACE VIEW ch05_flat_sheet (the LEFT JOIN of insurance_claims to telemedicine_usage using Patient_ID) and CREATE OR REPLACE VIEW reimbursement_by_coverage, which later tasks read; the check reads both. Compare the flat sheet's claimed total with the claims table's. Then build the version at one row per claim, with the sessions reduced first, and show in a note that its row count and claimed total match the claims table. Assign the blocks to Task 5.

Compute a rate of sums

Chapter 6. This task reads reimbursement_by_coverage from Task 5, which sits above it in the notebook. Run the chapter's queries. Compute the reimbursement rate of approved claims two ways, as the mean of per-claim rates and as the rate of sums, and save the rate of sums (100.0 times total reimbursed over total claimed, rounded to one decimal) as a view named ch06_answer with one column, approved_rate_pct. In a note, write two sentences saying which one a finance team means and why a view for a BI tool should carry the sums. Assign the blocks to Task 6.

Find the orphan sessions

Chapter 7. Run the chapter's queries; the scratch tables it creates are temporary and Run all rebuilds them. Count the sessions whose Doctor_ID is not a Worker_ID in healthcare_workforce, written with NOT EXISTS, and save the count as a view named ch07_answer with one column, orphan_sessions. Then write the sessions-by-department query twice, with an inner join and with a labeled left join, and in a note write one sentence for a report that states how many sessions the inner join loses. Assign the blocks to Task 7.

Test the keys and deduplicate

Chapter 8. Run the chapter's queries, including CREATE OR REPLACE VIEW claims_clean and sessions_clean; Tasks 9 to 17 read them, so keep their blocks above. The Workbench's tables are views over the files and have no rowid, so for the file-position query first run CREATE OR REPLACE TEMP TABLE ch08_claims AS SELECT * FROM insurance_claims and select rowid from ch08_claims. In a note, write two sentences for each table that fails the key test (what kind of duplicate it has, what should happen to it) and the survivorship rule for the claims in words. Assign the blocks to Task 8.

Predict and run the joins

Chapter 9. This task uses claims_clean and sessions_clean from Task 8. Before running anything, write in a note the row counts you expect from the inner, left, right, full and cross joins of the two views on Patient_ID, from three facts: 40 patients have both, one of them has two sessions, and each view has 100 rows. Run the chapter's queries, including CREATE OR REPLACE TEMP VIEW ch09_session_detail, which the check also looks for, and the five joins, and explain any difference. Save the row count of the full join as a view named ch09_answer with one column, full_join_rows. Assign the blocks to Task 9.

Keep the unknown doctors in the count

Chapter 10. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries. Count the sessions whose doctor is not known to work in Surgery, keeping the sessions whose doctor is unknown (with IS DISTINCT FROM, or with OR w.Department IS NULL), and save the count as a view named ch10_answer with one column, not_surgery_or_unknown. In a note, write the chapter's three one-sentence answers (WHERE and HAVING, the three forms of COUNT, UNION and UNION ALL), each followed by the block that proves it. Assign the blocks to Task 10.

Build a spine of weeks

Chapter 11. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries; the temporary objects of the section on temporary tables are rebuilt by Run all. Build the weekly spine of 2023 with a recursive CTE (53 weeks from Monday 26 December 2022), count the sessions in each week with a LEFT JOIN, counting the session key, and save the number of weeks with no session as a view named ch11_answer with one column, empty_weeks. In a note, list the empty weeks and give the average per week over all 53 weeks and over the busy weeks. Assign the blocks to Task 11.

Split events into episodes

Chapter 12. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries, including CREATE OR REPLACE VIEW patient_events and patient_sequence, which Task 14 reads and the check also looks for. Write the episode query yourself, where a gap of more than 90 days starts a new episode, and save the number of patients with more than one episode as a view named ch12_answer with one column, patients. In a note, give the counts at 60 and at 180 days and one sentence on how sensitive the count is to a number someone chose. Assign the blocks to Task 12.

Rewrite a slow query

Chapter 13. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries; the two million-row tables take a few seconds to build. Write the fourth-quarter query on claims_clean both ways: as the colleague wrote it (a join to sessions_clean and strftime) and as rewritten (a date range and EXISTS). Save the rewritten count as a view named ch13_answer with one column, q4_claims_with_session. In a note, explain in one sentence why the two counts differ, and name the step of Table 13-2 that would have found it. Assign the blocks to Task 13.

Do the take-home

Chapter 14. This task uses reimbursement_by_coverage (Task 5), claims_clean and sessions_clean (Task 8) and patient_events and patient_sequence (Task 12). Run the chapter's queries, starting with CREATE OR REPLACE VIEW coverage_review, which the check reads. Then do the take-home yourself: in notes, write the five clarifying questions and the assumptions you would state if nobody answered, the three checks on the rate with their numbers, and a summary of no more than one page. Note every place where you chose differently from the chapter, and whether you said so. Assign the blocks to Task 14.

Deliver two lists

Chapter 15. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries, including CREATE OR REPLACE VIEW denied_no_session and healsync_checks; the check reads both. Produce the outreach list and the over-reimbursed exception list in full, each with its one-sentence definition and its count in a note. Write the three-sentence message to the owner of the claims data, and add one row of your own to healsync_checks for something you noticed that the chapter does not list. Assign the blocks to Task 15.

Answer aloud

Chapter 16. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries. Then answer questions 2 and 5 again on patient_satisfaction and claims_clean: run the inner and left joins on Patient_ID, and UNION ALL and UNION of their patients, and save the UNION count as a view named ch16_answer with one column, patients. In a note, write the two spoken answers with their numbers, each under a minute when said aloud. Assign the blocks to Task 16.

Check a join and submit

Chapter 17. This task uses claims_clean and sessions_clean from Task 8. Run the chapter's queries. Then run the ten checks on the join of patient_satisfaction to insurance_claims on Patient_ID, with one row per survey as the intended grain, and save the row count of the inner join as a view named ch17_answer with one column, join_rows. In a note, explain why it is one more than the number of surveys whose patient has a claim and which check finds it, then repeat the join with claims_clean. Assign the blocks to Task 17, press Run all until it passes, and submit with a one-line description.