Skip to content

SQL and pandas for data rounds

  • Coding
  • Specialist topic or differentiator
  • About 2 h to learn
  • 3 vetted resources
Build a plan that includes this

What this topic covers

Many Applied AI loops include a data round: a CSV or two tables, and you have to join, group, rank and filter them live, either in SQL or in pandas. The questions are predictable: pick the right join and explain what happens to unmatched or duplicate keys, aggregate per group, and use window functions (row_number, rank, lag/lead, running totals) for top-n-per-group or change-over-time questions. Interviewers also check that you can translate between the two worlds, since eval pipelines and dataset curation are usually pandas while analytics lives in SQL. This topic covers the join/groupby/window core in both and the gotchas (null keys, cartesian blow-ups, rank ties) that trip people up.

Builds on

Leads to

You’re ready when you can

What interviewers check, from the resources below.

  • Write an inner, left and full join in both SQL and pd.merge, and say what happens to rows with duplicate or null keys in each
  • Explain the difference between size() and count() in a groupby, and when each matches SQL COUNT
  • Produce the top-n rows per group in both SQL (window function) and pandas (groupby + rank or head)
  • Reproduce UNION vs UNION ALL with concat and drop_duplicates
  • Explain what PARTITION BY and ORDER BY each do inside OVER(), and why a window function does not collapse rows like GROUP BY
  • Compare ROW_NUMBER, RANK and DENSE_RANK on a column with ties
  • Write a running total and a previous-row difference (LAG) for a per-user time series
  • Use NTILE to bucket rows into quartiles
  • State what shape agg, transform and filter each return relative to the input frame
  • Implement a per-group rank, cumulative sum and group-total column using transform instead of a merge

What to study, in order

Each link opened and checked by hand; the date says when.

  1. Start here

    Comparison with SQL (opens in a new tab)

    pandas documentation · pandas developers · 35 min · Free · checked 21 Oct 2026

    Why
    Data rounds let you answer in SQL or pandas, and this page is the shortest route to being fluent in both with the same mental model.
    What to read
    Read “GROUP BY”, “JOIN” (all four join subsections) and “pandas equivalents for some SQL analytic and aggregate functions” (both “Top n rows” subsections).
    What to skip
    “Copies vs. in place operations”, “UPDATE” and “DELETE”; skim “SELECT” and “WHERE” only if you're rusty.

  2. Free alternative

    SQL Window Functions: ROW_NUMBER, LAG, LEAD, NTILE (opens in a new tab)

    ThoughtSpot SQL Tutorial (formerly Mode) · ThoughtSpot · 30 min · Free · checked 21 Oct 2026

    Why
    Window functions are the step up from basic SQL that interviewers use to separate people who have only written SELECT-WHERE-GROUP BY from those who have done real analysis.
    What to read
    Read “Basic windowing syntax” through “LAG and LEAD” (covers SUM/COUNT/AVG over a window, ROW_NUMBER, RANK vs DENSE_RANK, NTILE, LAG/LEAD).
    What to skip
    “Defining a window alias”, “Advanced windowing techniques”, the embedded query editor and the FAQ.

  3. Go deeper

    Group by: split-apply-combine (opens in a new tab)

    pandas documentation · pandas developers · 45 min · Free · checked 21 Oct 2026

    Why
    The groupby aggregate/transform/filter distinction is the pandas equivalent of GROUP BY vs window functions, and candidates who blur the two write wrong or slow code under time pressure.
    What to read
    Read “Aggregation”, “Transformation” (including its window and resample part) and “Filtration”.
    What to skip
    “Iterating through groups”, “Flexible apply”, “Numba accelerated routines”, “Other useful features” and “Examples”.

Companies that test Coding most

Put SQL and pandas for data rounds in your plan

Tell us your interview date and hours a day; it lands on the right day, in the right order.

Build my plan