LEAD: Show Next Day's Sales Amount

SQL coding challenge · Difficulty: easy · Topic: Window Functions · +50 XP

Problem

The forecasting team pre-positions stock a day ahead, so every day's report needs

tomorrow's sales figure printed next to today's. Show each day's own sales

alongside the sales of the day that follows it.

Engine: queries run on MySQL 8.0, so use MySQL-compatible syntax and functions.

Tables

daily_sales — one row per day, in sale_id order. sale_id is unique.

| column | type | description |
| --- | --- | --- |
| sale_id | INT | orders the days; smaller means earlier |
| day_name | VARCHAR | the day's label |
| amount | INT | that day's sales; may be negative, or NULL if unrecorded |

Return every row of daily_sales, with one extra column:

• next_day_sales — the amount from the following row, or NULL if

there is no following row

Sort the result by sale_id ascending.

"Next" means the next row, not the next id

sale_id orders the days, but it does not promise to count 1, 2, 3, ….

Ids can jump — 1, 5, 9, 40 — and the day after sale_id 5 is then sale_id 9,

not some missing 6. Reach for *the next row in order*, never for

sale_id + 1; the hidden tests include a gapped set where those two answers

part company.

The last row has no successor

The final day reports NULL, not 0 and not its own amount. Nothing is

substituted there.

NULL can also arrive for a second reason: if the **next day's amount is

itself NULL**, this column is NULL too, even though a next day exists. Both

cases simply carry the value across as it is.

Order the window itself

The window has to be told what "next" means. It is not enough for the *outer*

query to sort by sale_id — that ordering belongs to how the result is

displayed, and a window left unordered reads rows in whatever order the engine

supplies them. It may happen to agree on one query and quietly disagree the

moment the outer sort changes or the query is nested. Put the ordering inside

the window, where it is actually guaranteed.

Example input

daily_sales

| sale_id | day_name | amount |
| --- | --- | --- |
| 1 | Mon | 100 |
| 2 | Tue | 200 |
| 3 | Wed | 300 |
| 4 | Thu | 400 |
| 5 | Fri | 500 |

Expected output

| sale_id | day_name | amount | next_day_sales |
| --- | --- | --- | --- |
| 1 | Mon | 100 | 200 |
| 2 | Tue | 200 | 300 |
| 3 | Wed | 300 | 400 |
| 4 | Thu | 400 | 500 |
| 5 | Fri | 500 | NULL |

Every row's next_day_sales is simply the amount printed one line below it.

Friday is last, so its cell is NULL — there is no Saturday row to look at.

What this SQL challenge teaches you

“LEAD: Show Next Day's Sales Amount” is a easy-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with LEAD, OVER, ORDER BY, Next row — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

• LEAD
• OVER
• ORDER BY
• Next row

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

1. Every row stays in the result and gains a column. You are peeking at a neighbouring row, not aggregating or joining -- SQL has a window function that does exactly this.
2. Order the window by sale_id, inside the OVER clause. Sorting only in the outer query is not the same thing: that decides how the result is printed, not which row the window considers 'next'.
3. Do not reach for sale_id + 1. Ids are ordered but not necessarily consecutive -- after 5 the next row might be 9. Ask for the next ROW, and gaps take care of themselves.
4. The last row must come back NULL. If you supply a default value the function will fill that cell instead, which is a different answer -- leave it out.
5. A NULL can mean two things here and both are correct: there is no next row, or the next row's amount is itself NULL. You do not need to tell them apart -- just carry the neighbouring value across as it is.

Where this comes up

Variations of this problem have been reported in interviews at Amazon, Flipkart, Databricks, Microsoft. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

• Top 3 Products per Category
• Running Total Revenue
• Median Salary per Department
• Latest Order Per Customer
• 3-Day Rolling Sum of Sales
• 7-Day Rolling Purchase Amount by Customer

Helpful resources

• Learn this concept
• Practice this topic
• All SQL practice questions
• Open the notebook

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated easy and covers Window Functions.

Solve this challenge free on PySpark.in