Mock Interview | SQL technical round | Episode 11 | Surfalytics
SQL mock interview: Nikita Volynets (data engineer) runs a live SQL session with Almat, who is preparing for a data engineer role. Three tasks in a B2B SaaS subscription setting: (1) SQL fundamentals — users per country, revenue per plan with filters, WHERE vs HAVING, JOINs; (2) CTE — revenue per country and countries above average, plus edge cases and year filter; (3) Window functions — LAG/LEAD for previous/next payment per user. Done in Hex notebooks with real data. Feedback and interview tips at the end. What you'll see • Intro and business context (B2B SaaS, subscriptions, payments). • Task 1: GROUP BY, ORDER BY, JOINs (payments → subscriptions → plans), SUM, WHERE vs HAVING, completed payments only, LEFT vs INNER JOIN. • Task 2: CTE (WITH), revenue per country, filter by overall average, discussion of users with no payments and filtering by year. • Task 3: Window functions — LAG and LEAD (PARTITION BY user_id, ORDER BY payment_date), previous/next payment and difference. • Feedback: what went well, what to improve (window functions, reading the question carefully). ⏱️ TIMESTAMPS 0:00 — Intro: Nikita, 3 tasks, goal 0:45 — Candidate intro: Almad 1:08 — Screen share, Hex notebook 2:43 — Business context 3:26 — Task 1: Subscription distribution 5:56 — Q1: Users per country 8:14 — Q2: Revenue per plan ($10k, completed) 15:12 — WHERE vs HAVING, completed filter 18:10 — LEFT vs INNER JOIN 20:08 — Task 2: CTE, revenue per country above average 21:23 — Building the CTE 26:23 — Explaining the solution 27:50 — Users with no payments (edge case) 32:38 — Filter by year (2023) 37:57 — Task 3: Window functions 41:35 — LAG and LEAD explained 45:43 — Feedback 49:03 — Wrap-up Resources • Surfalytics — FREE Data Engineering course and workshops • SQL Mock Interviews playlist: https://www.youtube.com/watch?v=qUwbpgbEymE&list=PLNCDg7zJiXhPD0ethFHC-1V1pkvWhc633 • Hex: https://hex.tech #sql #mockinterview #dataengineering #sqltutorial #cte #windowfunctions #lag #lead #surfalytics #dataengineer #sqlinterview