pandas & SQL — problem bank

group by key(s), use named aggregation to keep column names flat

df.groupby('dept').agg(
    avg_sal=('salary','mean'),
    max_sal=('salary','max')
).reset_index()

• named agg avoids the messy MultiIndex columns
• .agg({'col':['mean','max']}) for quick multi-func on one col
• always .reset_index() after, for a flat df

long→wide: pivot_table (auto-aggregates dupes). wide→long: melt

df.pivot_table(index='date', columns='cat',
    values='sales', aggfunc='sum')

df.melt(id_vars='date', var_name='cat',
    value_name='sales')

• plain .pivot() errors on duplicate index/col combos — pivot_table handles it
• melt is the exact reverse of pivot

inspect with duplicated(), remove with drop_duplicates()

df[df.duplicated(subset=['id'], keep=False)]

df.drop_duplicates(subset=['id'], keep='first')

• keep=False flags every copy — inspect before you drop
• always pass subset= explicitly, don't rely on full-row match

pick join type by what rows to keep, validate with indicator

df1.merge(df2, on='id', how='left',
    indicator=True)

• how='left' keeps all df1 rows, NaN-fills missing matches
• indicator=True adds a _merge col — great for debugging row-count surprises
• watch for duplicate keys on either side silently multiplying rows

sort by time first, then rolling().agg(), group for per-entity windows

df.sort_values('date')
  .groupby('store')['sales']
  .rolling(7).mean()

• rolling() needs sorted data — sort_values on the time col first, always
• min_periods= avoids NaNs at the start if partial windows are fine

DENSE_RANK() over ORDER BY, filter rnk = N — handles ties correctly

SELECT salary FROM (
  SELECT salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) rnk
  FROM employees
) t WHERE rnk = 2;

• DENSE_RANK skips no numbers on ties (1,1,2) — use RANK() for gaps instead
• LIMIT 1 OFFSET 1 is shorter but silently wrong when there are ties

SUM() as a window function ordered by the sequence column

SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date)
    AS running_total
FROM orders;

• window function, not GROUP BY — keeps every row instead of collapsing
• add PARTITION BY customer_id for a running total per group

GROUP BY the key, HAVING COUNT(*) > 1

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

• HAVING filters after aggregation — WHERE can't reference COUNT()
• to get the actual duplicate rows back, join this result to the base table

join on a date range (BETWEEN) instead of exact equality

SELECT o.order_id, p.price
FROM orders o
JOIN prices p
  ON o.product_id = p.product_id
  AND o.order_date
      BETWEEN p.start_date AND p.end_date;

• exact-equality joins fail when tables have different date granularity — range join fixes it
• overlapping ranges can produce duplicate matches — sanity-check row count after