Week 02

Hard Company Problems: Combining Everything

Day 113 hrs

Put the pieces together

A longer SQL question usually combines things you already know. The difficulty is keeping the business rules straight while joins and aggregations change what each row represents.

For example: "For every city, report orders, delivered orders and delivery rate, including cities with no orders. Sort by rate descending, then city ascending." The formula is simple. Keeping the right cities and counting each order once takes more care.

Before writing the query:

Copy the output contract from the bottom of the statement into a comment: column names, their order, sort keys with tie breakers, decimal format.
Mark which table is the "list of things" (cities, companies, students) and which are the "events" (orders, interviews, payments).
Underline every phrase that is secretly a rule: "including cities with zero orders", "count each student once".
Describe the steps in a few short sentences. Use a CTE where a step needs its own result.
Only now start typing, and type the first CTE only.

We will use a placement funnel and a retention report to practise this. The same approach works for orders, payments, subscriptions and other event data.

One scenario runs through this page: your college placement portal. Learn its tables once, reuse them everywhere.

What is a Multi-Step Business Question?

A report may need counts from several tables. Joining all the raw rows first can multiply them and inflate those counts. A useful approach is to calculate each measure at the required level, then join the results.

The placement portal. companies is the list of things:

company_id	company_name
1	Zoho
2	Freshworks
3	Deloitte

interviews is an event table, one row per student per round:

interview_id	student_id	company_id	round_type
1	101	1	TECHNICAL
2	101	1	HR
3	102	1	TECHNICAL
4	103	2	TECHNICAL
5	103	2	HR
6	104	2	HR

offers hangs off an interview, not off a student:

offer_id	interview_id	offer_status
1	2	ACCEPTED
2	5	REJECTED
3	6	ACCEPTED

Deloitte has no interviews at all, and student 102 has an interview but no offer. Sample data plants such rows deliberately.

The ask. Per company: how many students appeared, how many reached the HR round, how many offers were made, and the HR reach rate as a percentage.

First check what you are counting. Here COUNT(*) counts interview rounds, not students:

SQL

SELECT company_id, COUNT(*) AS interview_rows
FROM interviews
GROUP BY company_id;

company_id	interview_rows
1	3
2	3

Three interview rows for Zoho, three for Freshworks. But rows are not students, Rahul (101) sits in two of them. COUNT(DISTINCT student_id) counts each student once:

SQL

SELECT company_id,
       COUNT(*) AS interview_rows,
       COUNT(DISTINCT student_id) AS appeared
FROM interviews
GROUP BY company_id;

company_id	interview_rows	appeared
1	3	2
2	3	2

Zoho interviewed 2 different students across 3 rounds, so appeared is now correct.

Now add an inner join to offers and check the student count again:

SQL

SELECT i.company_id, COUNT(DISTINCT i.student_id) AS appeared
FROM interviews i
JOIN offers o ON o.interview_id = i.interview_id
GROUP BY i.company_id;

company_id	appeared
1	1
2	2

Zoho now says 1 student. Student 102 interviewed with Zoho but never got an offer, so the join threw his row away before the count happened. Side by side:

company	appeared (correct)	appeared (after joining offers)
Zoho	2	1
Freshworks	2	2

This is the whole reason the pattern below exists: build each number in its own small result, then stitch the results together on the key they share.

The Pattern

Read the output contract first. Column names in order, sort keys with tie breakers, the format of every numeric column. That is what the judge compares, everything above it is only a means to it.
Name the steps in English. "Per company, count distinct students and HR students." "Per company, count offers and accepted offers." "Attach both to the company list." "Compute the rate and sort." Four sentences means four steps.
Check each intermediate result. Use named CTEs for separate calculations. Run SELECT * FROM that_cte; while building the query and check that it has the expected rows and values.
Join the CTEs on the grouping key. LEFT JOIN from the side that must keep every row, which is almost always the list of things: companies, cities, students.
Format and sort at the end. Never let formatting leak into the joins.

For a quick check of the counting rule, find companies where at least 2 different students reached HR:

SQL

SELECT company_id, COUNT(DISTINCT student_id) AS hr_students
FROM interviews
WHERE round_type = 'HR'
GROUP BY company_id
HAVING COUNT(DISTINCT student_id) >= 2
ORDER BY company_id;

Read it line by line:

FROM interviews picks the table to read.
WHERE round_type = 'HR' keeps only HR rows, before any grouping.
GROUP BY company_id squeezes the survivors into one group per company.
HAVING COUNT(DISTINCT student_id) >= 2 drops whole groups, using a number that exists only after grouping.
SELECT picks the columns to print per surviving group.
ORDER BY company_id fixes the row order the judge sees.

Dry Run: how many rows survive each clause.

Step	Clause	Rows or groups after it	What is left
1	`FROM interviews`	6 rows	all interviews
2	`WHERE round_type = 'HR'`	3 rows	interviews 2, 5, 6
3	`GROUP BY company_id`	2 groups	company 1 (1 row), company 2 (2 rows)
4	`HAVING COUNT(DISTINCT ...) >= 2`	1 group	company 2 only
5	`SELECT`	1 row	`company_id 2, hr_students 2`
6	`ORDER BY company_id`	1 row	same row

So WHERE runs before grouping and HAVING after. A condition on a raw column goes in WHERE, one on an aggregate goes in HAVING. COUNT(...) inside WHERE is a straight syntax error, not a slow query.

Worked example 1: the placement funnel, one CTE at a time

Four sentences, four steps.

stage: per company, students and HR students
won: per company, offers and accepted
funnel: LEFT JOIN both onto companies
output: rate, PRINTF, ORDER BY

Step 1, stage. Only interviews is touched, offers does not exist yet.

SQL

SELECT company_id,
       COUNT(DISTINCT student_id) AS appeared,
       COUNT(DISTINCT CASE WHEN round_type = 'HR' THEN student_id END) AS reached_hr
FROM interviews
GROUP BY company_id

company_id	appeared	reached_hr
1	2	1
2	2	2

CASE WHEN cond THEN student_id END gives the student id for HR rounds and NULL otherwise, and COUNT skips NULLs, so the second column counts only HR students. Deloitte is already missing, it has no interviews. Remember that, it matters in step 3.

Step 2, won. Only offers is touched. interviews is joined only to learn which company an offer belongs to.

SQL

SELECT i.company_id,
       COUNT(*) AS offers_made,
       SUM(CASE WHEN o.offer_status = 'ACCEPTED' THEN 1 ELSE 0 END) AS offers_accepted
FROM offers o
JOIN interviews i ON i.interview_id = o.interview_id
GROUP BY i.company_id

company_id	offers_made	offers_accepted
1	1	1
2	2	1

Three offer rows became two company rows. Step 1 and step 2 never see each other, so the row loss above cannot happen here.

Step 3, funnel. Attach both aggregates to the company list. LEFT JOIN keeps every row of the left table even when the right side has no match, filling the missing columns with NULL, and COALESCE(x, 0) turns those NULLs into 0.

SQL

SELECT c.company_name,
       COALESCE(s.appeared, 0)        AS appeared,
       COALESCE(s.reached_hr, 0)      AS reached_hr,
       COALESCE(w.offers_made, 0)     AS offers_made,
       COALESCE(w.offers_accepted, 0) AS offers_accepted
FROM companies c
LEFT JOIN stage s ON s.company_id = c.company_id
LEFT JOIN won   w ON w.company_id = c.company_id

company_name	appeared	reached_hr	offers_made	offers_accepted
Zoho	2	1	1	1
Freshworks	2	2	2	1
Deloitte	0	0	0	0

Deloitte comes back with zeros. This checks the requirement to include companies with no interviews.

Step 4, output. Rate, formatting, sort. Deloitte has appeared = 0, so guard the division. The whole query, every CTE in place:

SQL

WITH stage AS (
    SELECT company_id,
           COUNT(DISTINCT student_id) AS appeared,
           COUNT(DISTINCT CASE WHEN round_type = 'HR' THEN student_id END) AS reached_hr
    FROM interviews
    GROUP BY company_id
),
won AS (
    SELECT i.company_id,
           COUNT(*) AS offers_made,
           SUM(CASE WHEN o.offer_status = 'ACCEPTED' THEN 1 ELSE 0 END) AS offers_accepted
    FROM offers o
    JOIN interviews i ON i.interview_id = o.interview_id
    GROUP BY i.company_id
),
funnel AS (
    SELECT c.company_name,
           COALESCE(s.appeared, 0)    AS appeared,
           COALESCE(s.reached_hr, 0)  AS reached_hr,
           COALESCE(w.offers_made, 0) AS offers_made
    FROM companies c
    LEFT JOIN stage s ON s.company_id = c.company_id
    LEFT JOIN won   w ON w.company_id = c.company_id
)
SELECT company_name, appeared, reached_hr, offers_made,
       CASE WHEN appeared = 0 THEN '0.00'
            ELSE PRINTF('%.2f', 100.0 * reached_hr / appeared) END AS hr_rate
FROM funnel
ORDER BY CASE WHEN appeared = 0 THEN 0 ELSE 100.0 * reached_hr / appeared END DESC,
         company_name ASC;

company_name	appeared	reached_hr	offers_made	hr_rate
Freshworks	2	2	2	100.00
Zoho	2	1	1	50.00
Deloitte	0	0	0	0.00

Nine event rows became two small aggregates, then the three lines the judge wants.

What the judge sees

Check the required columns, output order and formatting against the sample. The practice judge compares the SQLite result:

Text

company_name|appeared|reached_hr|offers_made|hr_rate
Freshworks|2|2|2|100.00
Zoho|2|1|1|50.00
Deloitte|0|0|0|0.00

So 100.0 instead of 100.00 is a failed case, and so is Deloitte's row appearing second. Two mistakes produce exactly that, and both look harmless in the editor.

Mistake A: sorting by the formatted string. ORDER BY hr_rate DESC sorts text, and there '5' beats '1':

wrong output		right output
Zoho	50.00	Freshworks	100.00
Freshworks	100.00	Zoho	50.00
Deloitte	0.00	Deloitte	0.00

Sort on the numeric expression, print the formatted one.

Mistake B: integer division. Write the rate as reached_hr / appeared * 100 and SQLite divides 1 by 2 as integers first:

company_id	wrong_rate	right_rate
1	0	50.0
2	100	100.0

Zoho's real 50 percent prints as 0 while Freshworks looks fine, so the sample passes and the hidden case fails. Multiply by 100.0 first.

Warning
Warning: In SQLite x / 0 is not an error, it silently returns NULL. PRINTF('%.2f', NULL) then prints 0.00, which looks right, while ROUND(NULL, 2) prints an empty cell. Guard the denominator with a CASE so you always know what you are printing.

The pre-submit checklist

Before you press Run, walk this list against the sample.

Column names spelled exactly as the statement spells them, in that order.
ORDER BY present, with every tie breaker the statement mentions.
Decimals: does 2.5 need to print as 2.50? Then PRINTF('%.2f', x).
Any division: is either side an integer? Is the denominator ever zero?
Zero rows kept with LEFT JOIN plus COALESCE, if the statement asked for them.
Row count exactly equal to the sample output.

Check what you learned

In the funnel above, you replace FROM companies c LEFT JOIN stage s with an INNER JOIN. What changes in the output?

Query Templates

SQL

-- 1. The standard 4-CTE pipeline: filter, aggregate, attach, format.
WITH eligible AS (          -- every business rule that decides "does this row count"
    SELECT b.* FROM bills b
    JOIN connections c ON c.id = b.connection_id
    WHERE b.cycle_month = '2024-02-01' AND b.state <> 'CANCELLED'
),
per_key AS (                -- one number per grouping key
    SELECT connection_id, SUM(amount) AS total FROM eligible GROUP BY connection_id
),
joined AS (                 -- attach back to the list of things, keeping zero rows
    SELECT d.id, d.name, COALESCE(p.total, 0) AS total
    FROM dim d LEFT JOIN per_key p ON p.connection_id = d.id
)
SELECT name, PRINTF('%.2f', total) AS total_amount
FROM joined
ORDER BY total DESC, name ASC;

-- 2. Funnel or conversion rate: one COUNT(DISTINCT CASE WHEN ...) per stage.
SELECT company_id,
       COUNT(DISTINCT student_id) AS reached_stage_1,
       COUNT(DISTINCT CASE WHEN round_type = 'HR' THEN student_id END) AS reached_hr
FROM interviews
GROUP BY company_id;

-- 3. Compare a row to its own group's average (join the aggregate back).
WITH grp AS (SELECT branch, year, AVG(ctc) AS avg_ctc
             FROM placements GROUP BY branch, year)
SELECT p.student_id
FROM placements p JOIN grp g ON g.branch = p.branch AND g.year = p.year
WHERE p.ctc > g.avg_ctc;

-- 4. Generate rows that do not exist in any table (pairing, gap filling).
WITH RECURSIVE seq(k, last_k) AS (
    SELECT 0, MAX(slot_no) / 2 FROM slots
    UNION ALL
    SELECT k + 1, last_k FROM seq WHERE k < last_k
)
SELECT l.title, r.title
FROM seq s
LEFT JOIN slots l ON l.slot_no = 2 * s.k
LEFT JOIN slots r ON r.slot_no = 2 * s.k + 1
ORDER BY s.k;

Tip
Tip: Name your CTEs after the sentence they implement: eligible_bills, per_company_offers, branch_year_avg. When the judge fails one case, the name tells you which step to go and print.

Variations

Variation 1 - Retention and cohorts

The second shape that repeats in OAs, usually worded "how many users came back within 7 days of signing up, month by month". Same portal, two more tables. students:

student_id	name	signup_date
101	Rahul	2024-07-05
102	Sneha	2024-07-20
103	Arjun	2024-08-02
104	Meena	2024-08-11
105	Vikram	2024-08-06

practice_log, one row per day a student practised:

student_id	log_date
101	2024-07-06
101	2024-07-09
102	2024-08-25
103	2024-08-04
105	2024-08-07

The requirement. Per signup month, count signups and students who practised at least once from signup day through day 7, inclusive. This example counts same-day activity; a return-visit question may exclude it.

cohort: tag every student with signup month
retained: DISTINCT students active inside the 7-day window
LEFT JOIN back and count per cohort

Step 1, cohort. No filtering, only labelling. STRFTIME('%Y-%m', d) cuts a date down to year and month. Five rows in, five rows out.

SQL

SELECT student_id, STRFTIME('%Y-%m', signup_date) AS cohort_month, signup_date
FROM students

student_id	cohort_month	signup_date
101	2024-07	2024-07-05
102	2024-07	2024-07-20
103	2024-08	2024-08-02
104	2024-08	2024-08-11
105	2024-08	2024-08-06

July cohort has 2 students, August has 3.

Step 2, retained. Keep only activity inside 0 to 7 days of that student's own signup. JULIANDAY(d) turns a date into a day number, so subtracting two gives the gap in days.

SQL

SELECT c.student_id
FROM cohort c
JOIN practice_log p ON p.student_id = c.student_id
WHERE JULIANDAY(p.log_date) - JULIANDAY(c.signup_date) BETWEEN 0 AND 7

student_id
101
101
103
105

Rahul qualifies but appears twice, he practised on Jul 6 and Jul 9. Sneha is out, her only activity is 36 days after signup. Add DISTINCT and you get one row per student:

student_id
101
103
105

Step 3, join back and count. COUNT(*) counts every student in the cohort, COUNT(r.student_id) only the ones that matched, because COUNT(col) skips NULLs and unmatched rows carry NULL on the right side.

SQL

WITH cohort AS (
    SELECT student_id, STRFTIME('%Y-%m', signup_date) AS cohort_month, signup_date
    FROM students
),
retained AS (
    SELECT DISTINCT c.student_id
    FROM cohort c
    JOIN practice_log p ON p.student_id = c.student_id
    WHERE JULIANDAY(p.log_date) - JULIANDAY(c.signup_date) BETWEEN 0 AND 7
)
SELECT c.cohort_month,
       COUNT(*) AS signups,
       COUNT(r.student_id) AS retained_users,
       PRINTF('%.2f', 100.0 * COUNT(r.student_id) / COUNT(*)) AS retention_pct
FROM cohort c
LEFT JOIN retained r ON r.student_id = c.student_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

cohort_month	signups	retained_users	retention_pct
2024-07	2	1	50.00
2024-08	3	2	66.67

1 of July's 2 students came back in time, 2 of August's 3 did.

That DISTINCT controls the row count. Drop it and Rahul's duplicate row duplicates his cohort row in the join, so even COUNT(*) grows:

cohort_month	signups	retained_users	retention_pct
2024-07	3	2	66.67
2024-08	3	2	66.67

July now claims 3 signups from a table that holds only 2 July students, and you cannot spot it without recounting by hand. For this report, the joined CTE needs at most one row per student so each signup is counted once.

Variation 2 - Rank after aggregating

You cannot rank on a value that does not exist yet. RANK() OVER (ORDER BY ...) is a window function: it numbers rows by looking at the rows around them, without collapsing them the way GROUP BY does. Aggregate first in a CTE, then rank over the CTE.

SQL

WITH per_company AS (
    SELECT c.company_name, COUNT(o.offer_id) AS offers_made
    FROM companies c
    LEFT JOIN interviews i ON i.company_id = c.company_id
    LEFT JOIN offers o ON o.interview_id = i.interview_id
    GROUP BY c.company_name
)
SELECT company_name, offers_made,
       RANK() OVER (ORDER BY offers_made DESC) AS rnk
FROM per_company
ORDER BY rnk, company_name;

company_name	offers_made	rnk
Freshworks	2	1
Zoho	1	2
Deloitte	0	3

Deloitte still gets a row and a rank, because the LEFT JOINs kept it. RANK() OVER (ORDER BY COUNT(o.offer_id) DESC) in one shot also works, but the CTE version is easier to debug: you can print per_company on its own.

Variation 3 - Pairing rows that are not adjacent

Seat pairs, cookbook pages, before and after snapshots. The rows you need exist nowhere in the data, so generate the skeleton with a recursive CTE and LEFT JOIN the real rows onto it twice, as in template 4 above.

IMP
Important: A recursive CTE without a terminating condition can keep producing rows until it is cancelled or hits an engine limit. Always bound it against a value computed in the anchor, like MAX(slot_no) / 2.

Check what you learned

Each connection has 3 bills for the cycle and 30 usage rows. You need eligibility from bills and total MB from usage. What do you write?

Common Mistakes

Joining two sets of detail rows before summing. Three bills joined to thirty usage rows can produce ninety rows. Aggregate each measure separately, or use EXISTS when you only need an eligibility check.
Losing entities with no activity. Start from the full entity list and use LEFT JOIN when the question asks to include them. Convert missing counts to zero where appropriate.
Integer division. 1 / 2 * 100 is zero in SQLite. Use 100.0 * numerator / denominator and decide how to handle a zero denominator.
Treating formatting as a rounding rule. PRINTF gives fixed decimal places; it does not make floating-point arithmetic exact. Follow any specific rounding rule in the statement separately.
Missing a sort key. Include each requested tie-break in the final ORDER BY.
Sorting a formatted number as text. Sort on the numeric expression, then display the formatted value. Text order puts '50.00' above '100.00'.
Copying syntax from another database. Functions such as SPLIT_PART and casts such as ::numeric are not SQLite syntax. Check the selected dialect before adapting a solution.

Interview Notes

How do you approach a long SQL question? I read the output contract first, then say the answer in three or four English sentences, and each sentence becomes one CTE. I verify each CTE alone before adding the next, and format only in the final SELECT. The follow up is usually "what if it is slow", where you talk about filtering early and indexing the join keys.

Why aggregate before joining? A join multiplies rows on the many side, so a SUM or COUNT after the join counts the same fact several times. I collapse each fact table to one row per key in its own CTE first, then join those results, so the join cannot inflate the arithmetic.

When do you use EXISTS instead of a JOIN? When the other table only answers yes or no, like "has this customer ever ordered". EXISTS is a semi join: it stops at the first match and never duplicates the driving row, while a JOIN there would repeat that row once per match.

How do you count a subset inside a GROUP BY? SUM(CASE WHEN cond THEN 1 ELSE 0 END) for a row count, COUNT(DISTINCT CASE WHEN cond THEN key END) for distinct entities. Both do it in one pass, so no second query is needed.

How do you compare a row to its own group's average? Either aggregate the group in a CTE and join it back on the grouping key, or use AVG(x) OVER (PARTITION BY group_col), which computes the group average and still keeps every row. I would choose the clearer form and compare execution plans if performance matters.

Your query is correct but one hidden case still fails. What do you check? Column names and their order, the tie breaker in ORDER BY, decimal formatting, and whether a zero activity row should be there. Then test the logic with duplicates, NULLs and missing matches.

Time management: 60 minutes, 2 SQL questions

One possible split is 25 minutes per question with 10 minutes for checks. First understand the required rows and measures, then build and verify each calculation. Adjust this if one question is clearly shorter.

If you are stuck, leave enough time for the other question. Whether a partial solution earns marks depends on the assessment; do not assume it will.

Quick Test

Check what you learned

The statement says "companies with no interviews must not be reported". Given the funnel pipeline above, what do you change?

Week 02

Hard Company Problems: Combining Everything

Day 113 hrs

Put the pieces together

A longer SQL question usually combines things you already know. The difficulty is keeping the business rules straight while joins and aggregations change what each row represents.

Before writing the query:

Copy the output contract from the bottom of the statement into a comment: column names, their order, sort keys with tie breakers, decimal format.
Mark which table is the "list of things" (cities, companies, students) and which are the "events" (orders, interviews, payments).
Underline every phrase that is secretly a rule: "including cities with zero orders", "count each student once".
Describe the steps in a few short sentences. Use a CTE where a step needs its own result.
Only now start typing, and type the first CTE only.

We will use a placement funnel and a retention report to practise this. The same approach works for orders, payments, subscriptions and other event data.

One scenario runs through this page: your college placement portal. Learn its tables once, reuse them everywhere.

What is a Multi-Step Business Question?

The placement portal. companies is the list of things:

company_id	company_name
1	Zoho
2	Freshworks
3	Deloitte

interviews is an event table, one row per student per round:

interview_id	student_id	company_id	round_type
1	101	1	TECHNICAL
2	101	1	HR
3	102	1	TECHNICAL
4	103	2	TECHNICAL
5	103	2	HR
6	104	2	HR

offers hangs off an interview, not off a student:

offer_id	interview_id	offer_status
1	2	ACCEPTED
2	5	REJECTED
3	6	ACCEPTED

Deloitte has no interviews at all, and student 102 has an interview but no offer. Sample data plants such rows deliberately.

The ask. Per company: how many students appeared, how many reached the HR round, how many offers were made, and the HR reach rate as a percentage.

First check what you are counting. Here COUNT(*) counts interview rounds, not students:

SQL

SELECT company_id, COUNT(*) AS interview_rows
FROM interviews
GROUP BY company_id;

company_id	interview_rows
1	3
2	3

Three interview rows for Zoho, three for Freshworks. But rows are not students, Rahul (101) sits in two of them. COUNT(DISTINCT student_id) counts each student once:

SQL

SELECT company_id,
       COUNT(*) AS interview_rows,
       COUNT(DISTINCT student_id) AS appeared
FROM interviews
GROUP BY company_id;

company_id	interview_rows	appeared
1	3	2
2	3	2

Zoho interviewed 2 different students across 3 rounds, so appeared is now correct.

Now add an inner join to offers and check the student count again:

SQL

SELECT i.company_id, COUNT(DISTINCT i.student_id) AS appeared
FROM interviews i
JOIN offers o ON o.interview_id = i.interview_id
GROUP BY i.company_id;

company_id	appeared
1	1
2	2

Zoho now says 1 student. Student 102 interviewed with Zoho but never got an offer, so the join threw his row away before the count happened. Side by side:

company	appeared (correct)	appeared (after joining offers)
Zoho	2	1
Freshworks	2	2

This is the whole reason the pattern below exists: build each number in its own small result, then stitch the results together on the key they share.

The Pattern

Read the output contract first. Column names in order, sort keys with tie breakers, the format of every numeric column. That is what the judge compares, everything above it is only a means to it.
Name the steps in English. "Per company, count distinct students and HR students." "Per company, count offers and accepted offers." "Attach both to the company list." "Compute the rate and sort." Four sentences means four steps.
Check each intermediate result. Use named CTEs for separate calculations. Run SELECT * FROM that_cte; while building the query and check that it has the expected rows and values.
Join the CTEs on the grouping key. LEFT JOIN from the side that must keep every row, which is almost always the list of things: companies, cities, students.
Format and sort at the end. Never let formatting leak into the joins.

For a quick check of the counting rule, find companies where at least 2 different students reached HR:

SQL

SELECT company_id, COUNT(DISTINCT student_id) AS hr_students
FROM interviews
WHERE round_type = 'HR'
GROUP BY company_id
HAVING COUNT(DISTINCT student_id) >= 2
ORDER BY company_id;

Read it line by line:

FROM interviews picks the table to read.
WHERE round_type = 'HR' keeps only HR rows, before any grouping.
GROUP BY company_id squeezes the survivors into one group per company.
HAVING COUNT(DISTINCT student_id) >= 2 drops whole groups, using a number that exists only after grouping.
SELECT picks the columns to print per surviving group.
ORDER BY company_id fixes the row order the judge sees.

Dry Run: how many rows survive each clause.

Step	Clause	Rows or groups after it	What is left
1	`FROM interviews`	6 rows	all interviews
2	`WHERE round_type = 'HR'`	3 rows	interviews 2, 5, 6
3	`GROUP BY company_id`	2 groups	company 1 (1 row), company 2 (2 rows)
4	`HAVING COUNT(DISTINCT ...) >= 2`	1 group	company 2 only
5	`SELECT`	1 row	`company_id 2, hr_students 2`
6	`ORDER BY company_id`	1 row	same row

Worked example 1: the placement funnel, one CTE at a time

Four sentences, four steps.

stage: per company, students and HR students
won: per company, offers and accepted
funnel: LEFT JOIN both onto companies
output: rate, PRINTF, ORDER BY

Step 1, stage. Only interviews is touched, offers does not exist yet.

SQL

SELECT company_id,
       COUNT(DISTINCT student_id) AS appeared,
       COUNT(DISTINCT CASE WHEN round_type = 'HR' THEN student_id END) AS reached_hr
FROM interviews
GROUP BY company_id

company_id	appeared	reached_hr
1	2	1
2	2	2

Step 2, won. Only offers is touched. interviews is joined only to learn which company an offer belongs to.

SQL

SELECT i.company_id,
       COUNT(*) AS offers_made,
       SUM(CASE WHEN o.offer_status = 'ACCEPTED' THEN 1 ELSE 0 END) AS offers_accepted
FROM offers o
JOIN interviews i ON i.interview_id = o.interview_id
GROUP BY i.company_id

company_id	offers_made	offers_accepted
1	1	1
2	2	1

Three offer rows became two company rows. Step 1 and step 2 never see each other, so the row loss above cannot happen here.

SQL

SELECT c.company_name,
       COALESCE(s.appeared, 0)        AS appeared,
       COALESCE(s.reached_hr, 0)      AS reached_hr,
       COALESCE(w.offers_made, 0)     AS offers_made,
       COALESCE(w.offers_accepted, 0) AS offers_accepted
FROM companies c
LEFT JOIN stage s ON s.company_id = c.company_id
LEFT JOIN won   w ON w.company_id = c.company_id

company_name	appeared	reached_hr	offers_made	offers_accepted
Zoho	2	1	1	1
Freshworks	2	2	2	1
Deloitte	0	0	0	0

Deloitte comes back with zeros. This checks the requirement to include companies with no interviews.

Step 4, output. Rate, formatting, sort. Deloitte has appeared = 0, so guard the division. The whole query, every CTE in place:

SQL

WITH stage AS (
    SELECT company_id,
           COUNT(DISTINCT student_id) AS appeared,
           COUNT(DISTINCT CASE WHEN round_type = 'HR' THEN student_id END) AS reached_hr
    FROM interviews
    GROUP BY company_id
),
won AS (
    SELECT i.company_id,
           COUNT(*) AS offers_made,
           SUM(CASE WHEN o.offer_status = 'ACCEPTED' THEN 1 ELSE 0 END) AS offers_accepted
    FROM offers o
    JOIN interviews i ON i.interview_id = o.interview_id
    GROUP BY i.company_id
),
funnel AS (
    SELECT c.company_name,
           COALESCE(s.appeared, 0)    AS appeared,
           COALESCE(s.reached_hr, 0)  AS reached_hr,
           COALESCE(w.offers_made, 0) AS offers_made
    FROM companies c
    LEFT JOIN stage s ON s.company_id = c.company_id
    LEFT JOIN won   w ON w.company_id = c.company_id
)
SELECT company_name, appeared, reached_hr, offers_made,
       CASE WHEN appeared = 0 THEN '0.00'
            ELSE PRINTF('%.2f', 100.0 * reached_hr / appeared) END AS hr_rate
FROM funnel
ORDER BY CASE WHEN appeared = 0 THEN 0 ELSE 100.0 * reached_hr / appeared END DESC,
         company_name ASC;

company_name	appeared	reached_hr	offers_made	hr_rate
Freshworks	2	2	2	100.00
Zoho	2	1	1	50.00
Deloitte	0	0	0	0.00

Nine event rows became two small aggregates, then the three lines the judge wants.

What the judge sees

Check the required columns, output order and formatting against the sample. The practice judge compares the SQLite result:

Text

company_name|appeared|reached_hr|offers_made|hr_rate
Freshworks|2|2|2|100.00
Zoho|2|1|1|50.00
Deloitte|0|0|0|0.00

So 100.0 instead of 100.00 is a failed case, and so is Deloitte's row appearing second. Two mistakes produce exactly that, and both look harmless in the editor.

Mistake A: sorting by the formatted string. ORDER BY hr_rate DESC sorts text, and there '5' beats '1':

wrong output		right output
Zoho	50.00	Freshworks	100.00
Freshworks	100.00	Zoho	50.00
Deloitte	0.00	Deloitte	0.00

Sort on the numeric expression, print the formatted one.

Mistake B: integer division. Write the rate as reached_hr / appeared * 100 and SQLite divides 1 by 2 as integers first:

company_id	wrong_rate	right_rate
1	0	50.0
2	100	100.0

Zoho's real 50 percent prints as 0 while Freshworks looks fine, so the sample passes and the hidden case fails. Multiply by 100.0 first.

Warning
Warning: In SQLite x / 0 is not an error, it silently returns NULL. PRINTF('%.2f', NULL) then prints 0.00, which looks right, while ROUND(NULL, 2) prints an empty cell. Guard the denominator with a CASE so you always know what you are printing.

The pre-submit checklist

Before you press Run, walk this list against the sample.

Column names spelled exactly as the statement spells them, in that order.
ORDER BY present, with every tie breaker the statement mentions.
Decimals: does 2.5 need to print as 2.50? Then PRINTF('%.2f', x).
Any division: is either side an integer? Is the denominator ever zero?
Zero rows kept with LEFT JOIN plus COALESCE, if the statement asked for them.
Row count exactly equal to the sample output.

Check what you learned

In the funnel above, you replace FROM companies c LEFT JOIN stage s with an INNER JOIN. What changes in the output?

Query Templates

SQL

-- 1. The standard 4-CTE pipeline: filter, aggregate, attach, format.
WITH eligible AS (          -- every business rule that decides "does this row count"
    SELECT b.* FROM bills b
    JOIN connections c ON c.id = b.connection_id
    WHERE b.cycle_month = '2024-02-01' AND b.state <> 'CANCELLED'
),
per_key AS (                -- one number per grouping key
    SELECT connection_id, SUM(amount) AS total FROM eligible GROUP BY connection_id
),
joined AS (                 -- attach back to the list of things, keeping zero rows
    SELECT d.id, d.name, COALESCE(p.total, 0) AS total
    FROM dim d LEFT JOIN per_key p ON p.connection_id = d.id
)
SELECT name, PRINTF('%.2f', total) AS total_amount
FROM joined
ORDER BY total DESC, name ASC;

-- 2. Funnel or conversion rate: one COUNT(DISTINCT CASE WHEN ...) per stage.
SELECT company_id,
       COUNT(DISTINCT student_id) AS reached_stage_1,
       COUNT(DISTINCT CASE WHEN round_type = 'HR' THEN student_id END) AS reached_hr
FROM interviews
GROUP BY company_id;

-- 3. Compare a row to its own group's average (join the aggregate back).
WITH grp AS (SELECT branch, year, AVG(ctc) AS avg_ctc
             FROM placements GROUP BY branch, year)
SELECT p.student_id
FROM placements p JOIN grp g ON g.branch = p.branch AND g.year = p.year
WHERE p.ctc > g.avg_ctc;

-- 4. Generate rows that do not exist in any table (pairing, gap filling).
WITH RECURSIVE seq(k, last_k) AS (
    SELECT 0, MAX(slot_no) / 2 FROM slots
    UNION ALL
    SELECT k + 1, last_k FROM seq WHERE k < last_k
)
SELECT l.title, r.title
FROM seq s
LEFT JOIN slots l ON l.slot_no = 2 * s.k
LEFT JOIN slots r ON r.slot_no = 2 * s.k + 1
ORDER BY s.k;

Tip
Tip: Name your CTEs after the sentence they implement: eligible_bills, per_company_offers, branch_year_avg. When the judge fails one case, the name tells you which step to go and print.

Variations

Variation 1 - Retention and cohorts

The second shape that repeats in OAs, usually worded "how many users came back within 7 days of signing up, month by month". Same portal, two more tables. students:

student_id	name	signup_date
101	Rahul	2024-07-05
102	Sneha	2024-07-20
103	Arjun	2024-08-02
104	Meena	2024-08-11
105	Vikram	2024-08-06

practice_log, one row per day a student practised:

student_id	log_date
101	2024-07-06
101	2024-07-09
102	2024-08-25
103	2024-08-04
105	2024-08-07

cohort: tag every student with signup month
retained: DISTINCT students active inside the 7-day window
LEFT JOIN back and count per cohort

Step 1, cohort. No filtering, only labelling. STRFTIME('%Y-%m', d) cuts a date down to year and month. Five rows in, five rows out.

SQL

SELECT student_id, STRFTIME('%Y-%m', signup_date) AS cohort_month, signup_date
FROM students

student_id	cohort_month	signup_date
101	2024-07	2024-07-05
102	2024-07	2024-07-20
103	2024-08	2024-08-02
104	2024-08	2024-08-11
105	2024-08	2024-08-06

July cohort has 2 students, August has 3.

Step 2, retained. Keep only activity inside 0 to 7 days of that student's own signup. JULIANDAY(d) turns a date into a day number, so subtracting two gives the gap in days.

SQL

SELECT c.student_id
FROM cohort c
JOIN practice_log p ON p.student_id = c.student_id
WHERE JULIANDAY(p.log_date) - JULIANDAY(c.signup_date) BETWEEN 0 AND 7

student_id
101
101
103
105

Rahul qualifies but appears twice, he practised on Jul 6 and Jul 9. Sneha is out, her only activity is 36 days after signup. Add DISTINCT and you get one row per student:

student_id
101
103
105

SQL

WITH cohort AS (
    SELECT student_id, STRFTIME('%Y-%m', signup_date) AS cohort_month, signup_date
    FROM students
),
retained AS (
    SELECT DISTINCT c.student_id
    FROM cohort c
    JOIN practice_log p ON p.student_id = c.student_id
    WHERE JULIANDAY(p.log_date) - JULIANDAY(c.signup_date) BETWEEN 0 AND 7
)
SELECT c.cohort_month,
       COUNT(*) AS signups,
       COUNT(r.student_id) AS retained_users,
       PRINTF('%.2f', 100.0 * COUNT(r.student_id) / COUNT(*)) AS retention_pct
FROM cohort c
LEFT JOIN retained r ON r.student_id = c.student_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

cohort_month	signups	retained_users	retention_pct
2024-07	2	1	50.00
2024-08	3	2	66.67

1 of July's 2 students came back in time, 2 of August's 3 did.

That DISTINCT controls the row count. Drop it and Rahul's duplicate row duplicates his cohort row in the join, so even COUNT(*) grows:

cohort_month	signups	retained_users	retention_pct
2024-07	3	2	66.67
2024-08	3	2	66.67

Variation 2 - Rank after aggregating

SQL

WITH per_company AS (
    SELECT c.company_name, COUNT(o.offer_id) AS offers_made
    FROM companies c
    LEFT JOIN interviews i ON i.company_id = c.company_id
    LEFT JOIN offers o ON o.interview_id = i.interview_id
    GROUP BY c.company_name
)
SELECT company_name, offers_made,
       RANK() OVER (ORDER BY offers_made DESC) AS rnk
FROM per_company
ORDER BY rnk, company_name;

company_name	offers_made	rnk
Freshworks	2	1
Zoho	1	2
Deloitte	0	3

Variation 3 - Pairing rows that are not adjacent

IMP
Important: A recursive CTE without a terminating condition can keep producing rows until it is cancelled or hits an engine limit. Always bound it against a value computed in the anchor, like MAX(slot_no) / 2.

Check what you learned

Each connection has 3 bills for the cycle and 30 usage rows. You need eligibility from bills and total MB from usage. What do you write?

Common Mistakes

Joining two sets of detail rows before summing. Three bills joined to thirty usage rows can produce ninety rows. Aggregate each measure separately, or use EXISTS when you only need an eligibility check.
Losing entities with no activity. Start from the full entity list and use LEFT JOIN when the question asks to include them. Convert missing counts to zero where appropriate.
Integer division. 1 / 2 * 100 is zero in SQLite. Use 100.0 * numerator / denominator and decide how to handle a zero denominator.
Treating formatting as a rounding rule. PRINTF gives fixed decimal places; it does not make floating-point arithmetic exact. Follow any specific rounding rule in the statement separately.
Missing a sort key. Include each requested tie-break in the final ORDER BY.
Sorting a formatted number as text. Sort on the numeric expression, then display the formatted value. Text order puts '50.00' above '100.00'.
Copying syntax from another database. Functions such as SPLIT_PART and casts such as ::numeric are not SQLite syntax. Check the selected dialect before adapting a solution.

Interview Notes

Time management: 60 minutes, 2 SQL questions

If you are stuck, leave enough time for the other question. Whether a partial solution earns marks depends on the assessment; do not assume it will.

Quick Test

Check what you learned

The statement says "companies with no interviews must not be reported". Given the funnel pipeline above, what do you change?