Data Analyst · SQL · Senior · Theory

You have a query with window functions over a billion-row events table, and it takes forty minutes. How would you investigate, and what would you optimize?

~4 min by voice

How to approach it

This checks whether the candidate can systematically diagnose a heavy analytical query: start with the plan, understand why a window is expensive (shuffle + sort), and reduce the cost architecturally, through data volume, the table's physical layout and incrementality, rather than "a bigger cluster".

Have you seen this question in a real interview?

Answers0

Avg. score—

Strong—

Practiced0

Topics

sql query optimization window functions

Similar questions

There's an orders table with the columns created_at and amount. Write a SQL query that finds anomalous days, where the daily revenue differs from the average over the previous seven days by more than three standard deviations. Keep in mind that on some days there were no orders at all. Senior A query that used to run in milliseconds now takes seconds after the table grew to tens of millions of rows. How would you investigate, and what can you do about it? Mid-level There's a sales table with the columns product_id, category, amount, and sold_at. Write a SQL query that, for each category, returns the three products with the highest revenue over the last thirty days, with the revenue total and the rank within the category. Mid-level What are a primary key and a foreign key? And how would you model a many-to-many relationship, for example students and courses? Junior In which cases would you deliberately denormalize a database schema, and what's the price you pay for it? Mid-level

Answer by voice

The interviewer will ask this question and listen. Think out loud and ask for a hint if you need one. When you're done, pause or tap “That's my answer”. In about a minute, your score and the points you covered show up here.

Best with headphones · ~4 min