SQL & Python — Interview Survival Pack

The two things you might actually be asked to write. Intuition + the lines that matter.
Built for Kalyani Baiju Sindhu

PART A · SQL
The order SQL actually runs (the #1 misunderstanding)
Intuition. You write SELECT first, but the engine runs it almost last. The true order is FROM → WHERE → GROUP
BY → HAVING → SELECT → ORDER BY → LIMIT. This explains two classic traps below.
In the room: SQL executes FROM and WHERE before SELECT, which is why you can't use a SELECT alias in
WHERE, and why row filtering uses WHERE but group filtering uses HAVING.

WHERE vs HAVING (guaranteed question)

Intuition. WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER aggregation. You cannot
put an aggregate like COUNT(*) in WHERE; that's HAVING's job.
-- customers with more than 5 orders
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed' -- row filter, before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5 -- group filter, after grouping
ORDER BY orders DESC;

Trap. 'Filter for groups with COUNT > 5' — if you reach for WHERE COUNT(*) > 5 it errors. The answer is HAVING.
They love this one.

JOINs — know these cold

Join Returns Mental picture
INNER Only rows matching in both tables. The overlap.
LEFT All left rows; NULLs where right has no match. Keep everyone on the left.
RIGHT All right rows; NULLs where left has no match. Keep everyone on the right.
FULL OUTER All rows from both; NULLs where no match. Everyone, matched or not.
Trap. 'Find customers with NO orders.' The senior answer is a LEFT JOIN ... WHERE [Link] IS NULL — the anti-
join pattern. Knowing this beats most candidates.
SELECT [Link], [Link]
FROM customers c
LEFT JOIN orders o ON o.customer_id = [Link]
WHERE [Link] IS NULL; -- customers who never ordered

GROUP BY + aggregates
COUNT, SUM, AVG, MIN, MAX collapse many rows into one per group. Every non-aggregated column in SELECT
must be in GROUP BY — say that rule out loud.

SQL & Python Pack — Page 1

Subqueries vs CTEs
Intuition. A CTE (WITH ... AS) is a named temporary result you define up top — it makes complex queries
readable and can be referenced multiple times. A subquery is a query nested inline. Same power; CTEs read
better and interviewers prefer them.
WITH monthly AS (
SELECT customer_id, DATE_TRUNC('month', created_at) AS mth,
SUM(amount) AS revenue
FROM orders GROUP BY 1, 2
)
SELECT * FROM monthly WHERE revenue > 1000;

Window functions (the senior differentiator)

Intuition. A window function computes across a set of rows WITHOUT collapsing them — unlike GROUP BY,
every row survives and gets an extra computed column. RANK, ROW_NUMBER, running totals, 'compare each
row to its group.'
-- top earner per department, keeping all rows
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees;
-- then filter WHERE rnk = 1 in an outer query

Trap. '2nd highest salary per department.' Window function with RANK/DENSE_RANK partitioned by dept, filter
rnk = 2. Doing this without a window function is painful — reaching for it signals real SQL.
ROW_NUMBER vs RANK vs DENSE_RANK: ROW_NUMBER is always unique (1,2,3,4); RANK skips after ties
(1,1,3); DENSE_RANK doesn't skip (1,1,2). One-line each, memorized.

Indexes & query optimization

Intuition. An index is like a book's index — it lets the database jump to rows instead of scanning every page.
Index the columns you filter and join on. The tradeoff: indexes speed reads but slow writes and use space.
In the room: If a query is slow I'd EXPLAIN it first to see if it's doing a full table scan, then index the columns in
the WHERE and JOIN conditions.

SQL curveball bank

They say You investigate / answer
Query is slow EXPLAIN plan → look for full scans → index filter/join cols → avoid SELECT *.
Duplicate rows appear A JOIN is fanning out on a one-to-many; check join keys, use DISTINCT or aggregate.
COUNT differs from expected NULLs — COUNT(col) ignores NULLs, COUNT(*) doesn't; check the join type too.
Find Nth highest Window function DENSE_RANK, filter = N.
Running total SUM(x) OVER (ORDER BY date) — a window, not GROUP BY.

SQL & Python Pack — Page 2

PART B · Python
Data structures — when to use which
Type Use when The trap
list Ordered, changeable sequence. Slow membership test (O(n)); use a set for
lookups.
tuple Fixed, unchangeable record. Immutable — can be a dict key, a list
cannot.
dict Key → value lookup. O(1) lookup; keys must be hashable
(immutable).
set Unique items, fast membership. Unordered; great for dedupe and 'is x in
here?'.

In the room: If I need fast 'is this present' checks or to remove duplicates, I reach for a set; if I need key-based
lookup, a dict — both are O(1) versus a list's O(n).

The Python one-liners they ask you to write

# count word frequency
from collections import Counter
freq = Counter([Link]())

# filter list of dicts where score > 0.8

high = [d for d in items if d['score'] > 0.8]

# sort list of dicts by a key, descending

ranked = sorted(items, key=lambda d: d['score'], reverse=True)

# reverse a string
rev = s[::-1]

# flatten / dedupe
unique = list(set(my_list))

Trap. If you blank on syntax while they watch, narrate the logic and name the tool: 'I'd use Counter from
collections — import might be slightly off but the logic is count occurrences.' Reasoning scores higher than silent
perfection.

Mutable vs immutable (a favourite gotcha)

Intuition. Lists, dicts, sets are mutable (changeable in place); strings, tuples, ints are immutable. This causes the
classic bug: a default mutable argument is shared across calls.
# BUG: default list is created once and reused
def add(x, items=[]): # don't do this
[Link](x); return items

# FIX
def add(x, items=None):
if items is None: items = []

SQL & Python Pack — Page 3

 [Link](x); return items

List comprehension, lambda, map/filter

Intuition. A comprehension is a compact loop that builds a list: [f(x) for x in xs if cond]. lambda is a one-line
anonymous function, mostly used as a key for sort/map/filter. Readable comprehensions are preferred over
map/filter in modern Python.

OOP four pillars (verbal answer ready)

Pillar One-line intuition
Encapsulation Bundle data + methods, hide internals behind an interface.
Abstraction Expose what it does, hide how — a simple surface over complexity.
Inheritance A child class reuses/extends a parent's behaviour.
Polymorphism Same method name, different behaviour per class (e.g. .area()).

Decorators, generators, context managers

Decorator. A function that wraps another to add behaviour (logging, timing, auth) without changing it — the @
syntax. 'It's a wrapper.'
Generator. A function with yield that produces items lazily, one at a time, instead of building a giant list in
memory — great for large data streams.
Context manager. The with block — guarantees setup/cleanup (like closing a file) even if an error occurs.

Concurrency — the 30-second truth

Intuition. Threading is good for I/O-bound work (waiting on network/disk); multiprocessing for CPU-bound work
(real parallel computation), because Python's GIL stops threads from running CPU code truly in parallel. async is
single-threaded cooperative multitasking, ideal for many concurrent I/O calls — like serving lots of API requests.
In the room: For waiting on many API calls I'd use async or threads; for heavy computation I'd use
multiprocessing, because the GIL prevents threads from parallelizing CPU work.
Trap. 'Why won't threads speed up my number-crunching?' The GIL — only one thread runs Python bytecode at
a time. Use multiprocessing for CPU-bound tasks.

Pandas essentials (analytics rounds)

[Link]('region')['revenue'].sum() # aggregate
df[df['amount'] > 100] # filter rows
[Link](other, on='id', how='left') # SQL-style join
df['col'].fillna(df['col'].median()) # handle missing
df.sort_values('date').head(10) # top N

Note the mapping: groupby = GROUP BY, merge = JOIN, boolean mask = WHERE. Saying 'pandas is SQL in Python'
shows you see the unity.

SQL & Python Pack — Page 4

Drill: cover the code blocks and rewrite each one-liner from memory. These five SQL patterns (HAVING, anti-join,
window rank, EXPLAIN, running total) and five Python one-liners cover the vast majority of what a 40-minute interview
can fit.

SQL & Python Pack — Page 5