Question library
Questions from real interviews. Drill any of them by voice in a couple of minutes, with an interviewer, a score and feedback.
What's the difference between the window functions ROW_NUMBER, RANK, and DENSE_RANK?JuniorThere's an orders table with the columns order_id, created_at, and amount. Write a SQL query that, for each day, returns the date, the revenue for that day, and the cumulative revenue since the start of the period, in ascending order of date.JuniorYou need to get each user's most recent order, and some users have several orders with the same timestamp. How would you write the query, and which function would you choose?Mid-levelThere'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-levelYou 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?SeniorThere'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