customer_id, order_id, amount and next_amount — that same customer's next order amount (NULL for their last), by customer then date. LEAD is LAG's mirror image, and interviewers check you know which is which.Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.
customers
5 cols
customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE
products
4 cols
product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL
orders
5 cols
order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT
order_items
4 cols
order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL
payments
5 cols
payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE
employees
6 cols
employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE
logins
2 cols
user_idINTEGERlogin_dateDATE
ratings
5 cols
rating_idINTEGERcustomer_idINTEGERproduct_idINTEGERratingINTEGERrating_dateDATE
subscriptions
6 cols
sub_idINTEGERcustomer_idINTEGERplanTEXTstart_dateDATEend_dateDATEmonthly_feeREAL
shipments
5 cols
shipment_idINTEGERorder_idINTEGERcarrierTEXTship_dateDATEdelivery_dateDATE
support_tickets
7 cols
ticket_idINTEGERcustomer_idINTEGERsubjectTEXTpriorityTEXTstatusTEXTopened_dateDATEclosed_dateDATE
coupons
5 cols
codeTEXTdiscount_pctREALmin_order_amountREALvalid_fromDATEvalid_toDATE
coupon_redemptions
4 cols
redemption_idINTEGERcodeTEXTorder_idINTEGERredeemed_dateDATE
LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id).
customers5 colscustomer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE| customer_id | name | city | country | signup_date |
|---|---|---|---|---|
| 101 | Rahul | Delhi | India | 2023-01-12 |
| 102 | Aman | Mumbai | India | 2023-02-03 |
| 103 | Sara | Bengaluru | India | 2023-02-20 |
products4 colsproduct_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 799 |
| 2 | Office Chair | Furniture | 5499 |
| 3 | Notebook | Stationery | 149 |
orders5 colsorder_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT| order_id | customer_id | order_date | amount | status |
|---|---|---|---|---|
| 1001 | 101 | 2024-01-05 | 1598 | Delivered |
| 1002 | 102 | 2024-01-11 | 5499 | Delivered |
| 1003 | 101 | 2024-02-02 | 149 | Cancelled |
order_items4 colsorder_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL| order_id | product_id | quantity | unit_price |
|---|---|---|---|
| 1001 | 1 | 2 | 799 |
| 1002 | 2 | 1 | 5499 |
| 1004 | 3 | 5 | 149 |
payments5 colspayment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE| payment_id | order_id | method | amount | paid_date |
|---|---|---|---|---|
| 5001 | 1001 | UPI | 1598 | 2024-01-05 |
| 5002 | 1002 | Card | 5499 | 2024-01-11 |
| 5003 | 1005 | UPI | 999 | 2024-03-02 |
employees6 colsemployee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE| employee_id | name | department | manager_id | salary | hire_date |
|---|---|---|---|---|---|
| 1 | Meera | Analytics | NULL | 180000 | |
| 2 | Rohit | Analytics | 1 | 95000 | |
| 3 | Neha | Engineering | 1 | 120000 |
logins2 colsuser_idINTEGERlogin_dateDATE| user_id | login_date |
|---|---|
| 101 | 2024-06-01 |
| 101 | 2024-06-02 |
| 102 | 2024-06-01 |
ratings5 colsrating_idINTEGERcustomer_idINTEGERproduct_idINTEGERratingINTEGERrating_dateDATE| rating_id | customer_id | product_id | rating | rating_date |
|---|---|---|---|---|
| 1 | 101 | 1 | 5 | 2024-01-10 |
| 2 | 101 | 4 | 4 | 2024-03-12 |
| 3 | 102 | 2 | 5 | 2024-01-15 |
subscriptions6 colssub_idINTEGERcustomer_idINTEGERplanTEXTstart_dateDATEend_dateDATEmonthly_feeREAL| sub_id | customer_id | plan | start_date | end_date | monthly_fee |
|---|---|---|---|---|---|
| 1 | 101 | Basic | 2023-02-01 | 2023-07-31 | 499 |
| 2 | 101 | Pro | 2023-08-01 | NULL | 999 |
| 3 | 102 | Pro | 2023-03-15 | 2024-03-14 | 999 |
shipments5 colsshipment_idINTEGERorder_idINTEGERcarrierTEXTship_dateDATEdelivery_dateDATE| shipment_id | order_id | carrier | ship_date | delivery_date |
|---|---|---|---|---|
| 1 | 1001 | Delhivery | 2024-01-06 | 2024-01-09 |
| 2 | 1002 | BlueDart | 2024-01-12 | 2024-01-14 |
| 15 | 1018 | Ekart | 2024-09-16 | NULL |
support_tickets7 colsticket_idINTEGERcustomer_idINTEGERsubjectTEXTpriorityTEXTstatusTEXTopened_dateDATEclosed_dateDATE| ticket_id | customer_id | subject | priority | status | opened_date | closed_date |
|---|---|---|---|---|---|---|
| 1 | 101 | Late delivery | High | Closed | 2024-01-10 | 2024-01-11 |
| 2 | 102 | Wrong item received | High | Closed | 2024-01-20 | 2024-01-23 |
| 10 | 110 | Wrong address delivered | Urgent | Open | 2024-05-01 | NULL |
coupons5 colscodeTEXTdiscount_pctREALmin_order_amountREALvalid_fromDATEvalid_toDATE| code | discount_pct | min_order_amount | valid_from | valid_to |
|---|---|---|---|---|
| WELCOME10 | 10 | 500 | 2024-01-01 | 2024-12-31 |
| SAVE20 | 20 | 2000 | 2024-03-01 | 2024-06-30 |
| FLASH50 | 50 | 1000 | 2024-04-01 | 2024-04-15 |
coupon_redemptions4 colsredemption_idINTEGERcodeTEXTorder_idINTEGERredeemed_dateDATE| redemption_id | code | order_id | redeemed_date |
|---|---|---|---|
| 1 | WELCOME10 | 1001 | 2024-01-05 |
| 2 | SAVE20 | 1006 | 2024-03-09 |
| 3 | WELCOME10 | 1013 | 2024-06-25 |