Skip to Content
SQL Intermediate: Queries That Survive Real Data
Intermediate
0 likes
Courses
SQL Intermediate: Queries That Survive Real Data

Check Yourself: Windows and Dates

Five scenario questions on ranking ties, running totals, date_trunc and time zones. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesPerformance Optimization Intermediate
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. Two customers tie for first place with 3 orders each, and the next customer has 2 orders. What do RANK and DENSE_RANK give the next customer?
Both give 3. RANK gives 2 and DENSE_RANK gives 3. Both give 2. RANK gives 3 and DENSE_RANK gives 2.
2. You use ROW_NUMBER to keep the first order of each customer, but two orders have the same date and the ORDER BY uses only the date. What is the risk?
ROW_NUMBER gives both rows the number 1. PostgreSQL raises an error for tied rows. Which of the tied rows gets number 1 is not guaranteed, so the result can change between runs. The tied rows are removed from the result.
3. A running total uses SUM(amount) OVER (ORDER BY day). Two rows share the same day, and both show the same running total. Why?
SUM ignores the second row. The default frame includes rows that tie with the current row on the ORDER BY value. The window function has a bug with dates. The two rows are in different partitions.
4. You write WHERE row_number() OVER (ORDER BY total) = 1 and PostgreSQL says window functions are not allowed in WHERE. What is the fix?
Move the condition to HAVING. Use a CROSS JOIN with the same table. Use LIMIT 1 and drop the window function. Compute the number in a subquery or CTE and filter in the outer query.
5. The same daily report for a timestamptz column shows different counts when run from Manila and from New York. What is the real fix?
Restart the server so every session agrees. Say which time zone's day you want inside the query, for example with AT TIME ZONE. Group by BETWEEN ranges instead of dates. Change the column to timestamp without time zone.

About

Five scenario questions on ranking ties, running totals, date_trunc and time zones. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
10/04/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.