SQL interview preparation: questions

Month-1 retention divides by the cohort's size: every user whose first activity fell in the cohort month, whether or not they come back. Its numerator is the cohort's users who were active in the next calendar month, each counted once. A month's churn rate divides by the users active in the previous month, whatever their cohort, and its numerator is those of them who were not active this month. The two are not complements in general: retention follows one cohort, churn follows everyone active the month before. In the lesson's data the January cohort's month-1 retention is 3 of 5 users, 60.0 %, and February's churn is 2 of January's 5 active users, 40.0 %; they add up to 100 % only because January's active users are exactly its cohort. In March, 3 of February's 7 active users churned: 42.9 %.

"Every" is relational division: the student needs an application for each of the recruiter's drives, so you check that no drive is missing, with a double NOT EXISTS or by comparing the student's count of distinct drives of that recruiter with the recruiter's number of drives. "Only" looks at the student's own applications instead: there must be at least one, and none may belong to another recruiter, so a student can qualify while missing some drives. "Never" is an anti-join: no application to any of the recruiter's drives, and students with no application at all qualify too. Write it with NOT EXISTS, because NOT IN returns nothing when its list holds a NULL. In the lesson's data, Aarav and Charan applied to every Kaveri drive, Bhavna and Charan only to Kaveri drives, and Esha and Farid never applied to one.

Two ranges fail to overlap in exactly two ways: one ends before or when the other starts, a.end <= b.start, or the other way round, b.end <= a.start. Negating "one or the other" gives "neither", so they overlap when a.end > b.start and b.end > a.start, which is the same as a.start < b.end AND b.start < a.end. Those two comparisons cover every case: partial overlap, one range inside the other and identical ranges. A half-open range includes its start and excludes its end, so a booking from 9:00 to 13:00 and one from 13:00 to 14:00 only touch: 13:00 < 13:00 is false and they do not overlap, which is what a hall's calendar wants. A test with <=, or BETWEEN, would flag them as a clash.

The grain is what one row of the result stands for: one row per city, per city and month, per customer. Stating it first fixes the GROUP BY, and it tells you how every source table must be reduced before the tables meet. A join changes the grain of whatever it joins: orders joined to their payments have one row per payment, so an order paid in two parts is counted twice and its delivery fee added twice. So aggregate each table at its own grain, orders per city and payments per city, and join the summaries. The grain also says which groups must appear: a report with one row per city should list every city, so it starts from the cities table with a LEFT JOIN, and a city without orders shows 0 orders and no shares.