Date Problems

Problem 1: Latest 2020 Login

Problem Statement

You are building a user activity dashboard and want to show each user's most recent login, but only looking at the year 2020.

Business context A yearly activity review asks: "When was each user last active during 2020?" Users who did not log in at all in 2020 should not appear.

Goal Find the most recent login in the year 2020 for each user. Exclude users who did not log in during 2020.

Input A Logins table with one row per login.

Output user_id and last_stamp (the latest 2020 login time). The result can be in any order.

Table Structure

Table: Logins

Column Meaning

• user_id — the ID of the user who logged in
• time_stamp — the exact date and time of the login

Important Notes

• The pair (user_id, time_stamp) is the primary key. A user can have many logins, but never two with the same timestamp.
• time_stamp is a DATETIME, so it has a time part. This matters for the year filter.
• The 2020 filter must be applied before finding the latest login. If you find the overall latest login first and filter after, users with later logins (in 2021, for example) are lost.
• A user with no 2020 login has no row in the result.
• The output keeps the full time, not only the date (2020-06-30 15:06:07).

Example

Input: Logins

Expected Output

CREATE TABLE Logins (user_id INTEGER, time_stamp TEXT);
INSERT INTO Logins VALUES
  (6, '2020-06-30 15:06:07'),
  (6, '2021-04-21 14:06:06'),
  (6, '2019-03-07 00:18:15'),
  (8, '2020-02-01 05:10:53'),
  (8, '2020-12-30 00:46:50'),
  (2, '2020-01-16 02:49:50'),
  (2, '2019-08-25 07:59:08'),
  (14, '2019-07-14 09:00:00'),
  (14, '2021-01-06 11:59:59');

Explanation

• User 6: logins in 2019, 2020, and 2021. Only the 2020 login counts, so the latest is 2020-06-30.
• User 8: two logins in 2020. The later one is 2020-12-30.
• User 2: only one login in 2020 (2020-01-16). The 2019 login is ignored.
• User 14: no login in 2020, so the user is excluded.

Extra Example (boundary moments)

Output:

The last second of 2020 and the first second of 2020 both belong to 2020. The logins one second outside the year are correctly ignored.

Intuition

There are two questions in one: "Which logins happened in 2020?" and "For each user, which of those is the latest?" Answer them in that order. First throw away every login outside 2020. Then, for each user, keep the biggest (latest) time stamp that is left.

Mental Model

Picture every user's logins as dots on a timeline. Draw a frame around the year 2020. Ignore every dot outside the frame. For each user, point at the right-most dot inside the frame. Users with no dot inside the frame do not get pointed at.

 Timeline:   2019 | ............ 2020 ............ | 2021
                  [ frame: >= 2020-01-01 and < 2021-01-01 ]

 User 6:   o (2019)        o (Jun 30)  <- latest in frame        o (2021)
 User 8:              o (Feb 1)    o (Dec 30)  <- latest in frame
 User 2:   o (2019)   o (Jan 16)  <- latest in frame
 User 14:  o (2019)                                              o (2021)   (nothing in frame)

Thought Process

1. Which rows matter? Only rows from 2020. That is a row-level filter, so it goes in WHERE (before grouping).
2. How do I pick the latest per user? GROUP BY user_id with MAX(time_stamp).
3. How are users without 2020 logins removed? Automatically: after the WHERE filter, they have no rows left, so they form no group.
4. How should the year filter be written? The given solution uses YEAR(time_stamp) = 2020. A range is better on large tables, because it does not wrap the column in a function.

Approach

Step 1: Keep only 2020 logins Filter with WHERE YEAR(time_stamp) = 2020 (or the range version).

Step 2: Group by user GROUP BY user_id.

Step 3: Take the latest time stamp in each group MAX(time_stamp) AS last_stamp.

SQL Query

Version 1: as given

SELECT
    user_id,
    MAX(time_stamp) AS last_stamp      -- the latest login among the 2020 rows
FROM Logins
WHERE YEAR(time_stamp) = 2020          -- keep only logins made in 2020
GROUP BY user_id;                      -- one result row per user

Version 2: index-friendly (recommended for large tables)

SELECT
    user_id,
    MAX(time_stamp) AS last_stamp
FROM Logins
WHERE time_stamp >= '2020-01-01 00:00:00'     -- start of 2020 (included)
  AND time_stamp <  '2021-01-01 00:00:00'     -- start of 2021 (not included)
GROUP BY user_id;

Both versions return the same result. Version 2 leaves time_stamp bare, so an index on it can be used (see section 2 of the concept notes).

Query Explanation

• WHERE YEAR(time_stamp) = 2020 — keeps only rows whose year is 2020. WHERE runs before GROUP BY, so rows from other years never reach the grouping step.
• The range version — >= '2020-01-01 00:00:00' and < '2021-01-01 00:00:00' is a half-open interval that covers every moment of 2020, including 2020-12-31 23:59:59.
• GROUP BY user_id — puts all remaining rows of one user together.
• MAX(time_stamp) — inside each group, returns the latest time stamp.
• There is no HAVING, because users without 2020 logins have already disappeared at the WHERE step.

Dry Run

Original table (9 rows)

↓

Step 1: WHERE (keep only 2020 rows)

↓

Step 2: GROUP BY user_id (the rows left)

(User 14 has no rows left, so there is no group for them.)

↓

Step 3: MAX(time_stamp) in each group

SQL Execution Flow

FROM Logins
    |
WHERE YEAR(time_stamp) = 2020        (drops rows outside 2020)
    |
GROUP BY user_id
    |
MAX(time_stamp) for each group
    |
SELECT user_id, last_stamp

Time Complexity

Time Complexity: O(n) for the scan and filter, plus grouping (O(m log m) for the m rows left after the filter) With Version 2 and an index on time_stamp, the database can jump straight to the 2020 rows instead of reading the whole table.

Space Complexity: O(u), where u is the number of users with a 2020 login The database keeps one running maximum per user.

Why: Filtering early with WHERE shrinks the data before grouping, which is always cheaper than grouping everything first.

Common Mistakes to Avoid

Mistake 1: Using BETWEEN with date-only values

Why it happens: "The whole year 2020" sounds like '2020-01-01' to '2020-12-31'.

Wrong:

WHERE time_stamp BETWEEN '2020-01-01' AND '2020-12-31'

The end value means 2020-12-31 00:00:00, so every login after midnight on Dec 31 is missed. In the extra example, user 20's login at 2020-12-31 23:59:59 would be lost.

Correct: Use >= '2020-01-01' AND < '2021-01-01'.

Mistake 2: Filtering with HAVING on the overall maximum

Why it happens: HAVING feels natural after GROUP BY.

Wrong:

SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
GROUP BY user_id
HAVING YEAR(MAX(time_stamp)) = 2020;

MAX runs over all years. User 6's overall latest login is in 2021, so user 6 is wrongly removed, even though they have a 2020 login.

Correct: Filter the rows in WHERE first, then take the MAX.

Mistake 3: Using 23:59:59 as the end of the year

Why it happens: It looks like the last second.

Wrong:

AND time_stamp <= '2020-12-31 23:59:59'

Logins with fractional seconds after 23:59:59 are missed.

Correct: Use < '2021-01-01 00:00:00'.

Mistake 4: Forgetting GROUP BY

Why it happens: MAX alone looks enough.

Wrong:

SELECT user_id, MAX(time_stamp) FROM Logins WHERE YEAR(time_stamp) = 2020;

MAX covers the whole table, and mixing it with a plain column errors in strict mode.

Correct: Add GROUP BY user_id.

Mistake 5: Using MIN instead of MAX

Why it happens: Mixing up "first" and "latest".

Result: the first 2020 login is returned, and user 8 would show 2020-02-01 05:10:53.

Correct: "Most recent" means MAX.

Mistake 6: Wrapping the column in YEAR() on a huge table

Why it happens: It is short and it works.

Why it hurts: The index on time_stamp may not be used.

Correct: Use the range version for large tables.

Interview Follow-up Questions

• "Why is BETWEEN '2020-01-01' AND '2020-12-31' wrong here?"
• "Why does the range version run faster than YEAR(time_stamp) = 2020 on a large table?"
• "How would you also return the number of 2020 logins for each user?" (Add COUNT(*).)
• "How would you return users who logged in 2019 but not in 2020?"
• "How would you return the first login in 2020 and the latest in 2020 together?"

Similar Problems

• Each user's first and latest login (section 5 of the concept notes)
• Orders in a month or a year (date ranges in section 3)
• Any "latest record per group within a time window" report

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• "Latest (or earliest) X per group, but only inside a time window" → filter the window in WHERE, then GROUP BY with MAX (or MIN)
• Time window on a DATETIME column → half-open interval, never BETWEEN with date-only values
• Users with no rows in the window must be left out → the WHERE filter does it automatically

Problem 2: Warmer Days

Problem Statement

A weather monitoring system stores daily temperatures. The meteorological team wants to find the days that were warmer than the day before.

Business context A rise from one day to the next is a basic signal for trends, heat alerts, and forecasts.

Goal Find the id of every record whose temperature is higher than the temperature recorded on the previous calendar day.

Input A Weather table with one row per date.

Output The id of each such day. The result can be in any order.

Table Structure

Table: Weather

Column Meaning

• id — a unique ID for each record, and the primary key
• recordDate — the date the temperature was recorded
• temperature — the temperature on that date

Important Notes

• No two rows share the same recordDate.
• "Previous day" means the previous calendar date (recordDate - 1 day), not the previous row and not id - 1. The id values may not follow the date order.
• If the previous day has no record (a gap in the dates), the day cannot be compared, so it is not in the result.
• The first date in the table has no previous day, so it is never in the result.
• The comparison is strict: only >, so equal temperatures do not count.
• Month and year ends must work correctly (for example, March 1 follows February 28 or 29).

Example

Input: Weather

Expected Output

CREATE TABLE Weather (id INTEGER, recordDate TEXT, temperature INTEGER);
INSERT INTO Weather VALUES
  (1, '2023-06-01', 18),
  (2, '2023-06-02', 22),
  (3, '2023-06-03', 20),
  (4, '2023-06-04', 28);

Explanation

• June 2 (id 2): 22 is higher than June 1's 18, so it is included.
• June 3 (id 3): 20 is not higher than June 2's 22, so it is excluded.
• June 4 (id 4): 28 is higher than June 3's 20, so it is included.

Extra Example 1 (a gap in the dates)

Output:

Day 3 (June 4, 30 degrees) has no record for June 3, so it is skipped, even though 30 is higher than 22. Day 4 (June 5, 25 degrees) is not higher than June 4's 30.

Extra Example 2 (month boundary)

Output: id 2. Feb 1 follows Jan 31, and 12 is higher than 10.

Intuition

To compare a day with the day before, each row needs to "see" the row for the previous date. A table cannot look at another row by itself, so we join the table to itself. One copy plays "today", the other copy plays "yesterday", and the join condition links each date to the date one day earlier.

Mental Model

Picture a paper calendar printed twice. Put the second copy next to the first, but shifted by one day so that each day of the first copy sits beside the previous day of the second copy. Now read across each row and ask: is today's number bigger than yesterday's?

 Today (w1)              Yesterday (w2)
 -------------------     -------------------
 06-02   22       <----  06-01   18      22 > 18 -> keep id 2
 06-03   20       <----  06-02   22      20 > 22 -> no
 06-04   28       <----  06-03   20      28 > 20 -> keep id 4
 06-01   18       <----  (05-31 missing)   no partner -> dropped

Thought Process

1. Do I need to compare two different rows? Yes: today's row and yesterday's row. So a self join.
2. How do I match today with yesterday? Today's date must equal yesterday's date plus one day: w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY).
3. Why DATE_ADD and not + 1? In MySQL, date + 1 is not "add a day". DATE_ADD handles month and year ends correctly.
4. Where does the temperature comparison go? After the dates are matched, WHERE w1.temperature > w2.temperature.
5. What do I return? w1.id, the id of the warmer (today) row.

Approach

Step 1: Self join the Weather table w1 is the current day. w2 is the previous day.

Step 2: Match consecutive dates w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY).

Step 3: Compare temperatures Keep pairs where w1.temperature > w2.temperature.

Step 4: Return the id of the current day SELECT w1.id.

SQL Query

SELECT w1.id                                   -- id of the warmer (current) day
FROM Weather w1                                -- w1 = today
JOIN Weather w2                                -- w2 = yesterday
  -- w1's date must be exactly one day after w2's date
  ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
-- keep only the pairs where today is warmer than yesterday
WHERE w1.temperature > w2.temperature;

Query Explanation

• FROM Weather w1 JOIN Weather w2 — the same table twice, with two aliases (the self join pattern from the Joins notes).
• ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY) — links each "today" row to the row for the day before. The function is applied to w2's date, and w1.recordDate stays bare.
• WHERE w1.temperature > w2.temperature — the comparison the question asks for. Because this is an INNER JOIN, putting it in WHERE or in ON gives the same result.
• SELECT w1.id — the id of today's row, not yesterday's.

An equally valid way to match the dates is DATEDIFF(w1.recordDate, w2.recordDate) = 1.

Dry Run

Original table

↓

Step 1: For each w1 row, find the w2 row that is one day earlier

↓

Step 2: Compare w1.temperature > w2.temperature

↓

Final output

SQL Execution Flow

FROM Weather w1
   |
JOIN Weather w2  ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
   |   (each day is paired with the day before it, if that day exists)
   |
WHERE w1.temperature > w2.temperature
   |
SELECT w1.id

Time Complexity

Time Complexity: O(n) with an index on recordDate; otherwise up to O(n²) Each row needs one lookup for the previous date. With an index on recordDate, each lookup is fast. Without it, the join may compare many pairs.

Space Complexity: O(1) extra space beyond the output No grouping or sorting is needed.

Why: The join condition is a date equality, which is ideal for an index. A condition like "temperature is higher" is applied afterward, on the few matched pairs.

Common Mistakes to Avoid

Mistake 1: Using id instead of the date to find the previous day

Why it happens: id - 1 looks like "the previous row".

Wrong:

ON w1.id = w2.id + 1

The ids may not follow the date order, and dates may have gaps. The query compares the wrong days.

Correct: Match on the calendar date with DATE_ADD.

Mistake 2: Writing recordDate + 1

Why it happens: The problem's pseudo-code shows "+ 1 day", and adding 1 is tempting.

Wrong:

ON w1.recordDate = w2.recordDate + 1

In MySQL this turns the date into a number like 20230630 and adds 1 to get 20230631, which is not a real date. It breaks at month ends.

Correct: DATE_ADD(w2.recordDate, INTERVAL 1 DAY).

Mistake 3: Comparing with the next day instead of the previous day

Why it happens: The two directions look similar.

Wrong:

ON w2.recordDate = DATE_ADD(w1.recordDate, INTERVAL 1 DAY)

Now w2 is tomorrow, so the query finds days that are warmer than the next day.

Correct: w1 (today) must equal w2 (yesterday) plus one day.

Mistake 4: Returning w2.id

Why it happens: Mixing up the aliases.

Result: the id of the cooler, earlier day is returned instead of the warmer day.

Correct: SELECT w1.id.

Mistake 5: Using >=

Why it happens: "Higher" is read loosely.

Result: days with the same temperature as the day before are included.

Correct: Use strict >.

Mistake 6: Forgetting the date condition in the join

Why it happens: Only the temperature comparison is remembered.

Wrong:

FROM Weather w1 JOIN Weather w2 ON w1.temperature > w2.temperature

Every day is compared with every cooler day, not just the day before.

Correct: Always include the date match in ON.

Mistake 7: Expecting days after a gap or the first day to appear

Why it happens: Assuming every day has a previous record.

Why it matters: A day whose previous date has no record, and the very first day, have no partner in the join, so they are (correctly) left out.

Interview Follow-up Questions

• "Why DATE_ADD(..., INTERVAL 1 DAY) and not + 1?"
• "How would you solve this with a window function like LAG()?" (A later topic. LAG(temperature) reads the previous row, but it assumes no date gaps unless you check them.)
• "What happens with missing dates?"
• "How would you find days colder than the day before?"
• "How would you find days warmer than the average of the previous 3 days?"

Similar Problems

• Find Team Size and Employees Earning More Than Their Manager (self joins in the Joins notes)
• Minimum Distance Between Points (a self join with a condition other than equality)
• Day-over-day or month-over-month growth reports

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• "Compared with the previous day (or month, or row)" → self join with a date shift, or LAG() with window functions
• Previous calendar day is needed → match on the date with DATE_ADD, never on id - 1
• Adding time to a date in MySQL → DATE_ADD, never + 1

Problem 3: Inactive Sellers

Problem Statement

You run a web store and want to find sellers who were inactive in 2020: they had no sales during that year. They may need a re-engagement message or may be deactivated.

Business context Sellers who stop selling cost the platform attention but bring in no revenue. Finding them lets the team decide whether to contact them or close their accounts.

Goal Get the names of all sellers who made no sales during 2020.

Input Three tables: Customer, Orders, and Seller. Only Orders and Seller are needed.

Output seller_name, sorted in ascending order.

Table Structure

Table: Customer

Table: Orders

Table: Seller

Column Meaning

• customer_id (Customer) — a unique number for each customer, and the primary key
• customer_name — the customer's name
• order_id — a unique ID for each order, and the primary key of Orders
• sale_date — the date the order was placed
• order_cost — the total cost of the order
• customer_id (Orders) — the customer who made the purchase
• seller_id (Orders) — the seller who sold the product; it links to the Seller table
• seller_id (Seller) — a unique ID for each seller, and the primary key
• seller_name — the seller's name

Important Notes

• The Customer table is part of the setup, but the question does not need it. Only sellers and their orders matter.
• A seller can have sales in some years and none in 2020. Such a seller is inactive in 2020 (Frank, who sold only in 2019).
• A seller with no orders at all is also inactive in 2020, and must appear in the result.
• sale_date is a DATE (no time part), so a year filter is safe with either a range or YEAR().
• The result must be sorted by seller_name ascending.

Example

Input: Customer

Input: Orders

Input: Seller

Expected Output

CREATE TABLE Customer (customer_id INTEGER, customer_name TEXT);
INSERT INTO Customer VALUES
  (101, 'Alice'),
  (102, 'Bob'),
  (103, 'Charlie');
CREATE TABLE Orders (order_id INTEGER, sale_date TEXT, order_cost INTEGER, customer_id INTEGER, seller_id INTEGER);
INSERT INTO Orders VALUES
  (1, '2020-03-01', 1500, 101, 1),
  (2, '2020-05-25', 2400, 102, 2),
  (3, '2019-05-25', 800, 101, 3),
  (4, '2020-09-13', 1000, 103, 2),
  (5, '2019-02-11', 700, 101, 2);
CREATE TABLE Seller (seller_id INTEGER, seller_name TEXT);
INSERT INTO Seller VALUES
  (1, 'Daniel'),
  (2, 'Elizabeth'),
  (3, 'Frank');

Explanation

• Daniel (ID 1): sold on 2020-03-01, so he is active and excluded.
• Elizabeth (ID 2): sold on 2020-05-25 and 2020-09-13 (and also in 2019), so she is active and excluded.
• Frank (ID 3): sold only in 2019, so he is included.

Extra Example (a seller with no orders, and boundary dates)

Add these sellers and orders to the example:

Output:

• Grace sold only on 2021-01-01, which is outside 2020, so she is inactive.
• Henry sold on 2020-12-31, the last day of 2020, so he is active.
• Ivy has no orders at all, so she is inactive.

Intuition

"Sellers with no sales in 2020" is a question about something missing. The classic way to find what is missing is a LEFT JOIN: list every seller, attach their 2020 orders, and look for sellers that ended up with nothing attached.

The most important detail is where the year condition goes. It must be part of the join condition (ON), so it decides which orders get attached. Sellers with no 2020 order are then kept, with empty (NULL) order columns.

Mental Model

Give every seller a blank folder. Put into each folder only the 2020 orders that belong to that seller. Then open all folders: a folder that is still empty belongs to an inactive seller.

 Seller folders after attaching only 2020 orders:

 Daniel     [ order 1 ]                  -> has 2020 sales (active)
 Elizabeth  [ order 2, order 4 ]         -> has 2020 sales (active)
 Frank      [ ]  (his order is from 2019) -> EMPTY -> inactive

Thought Process

1. Which list must be complete? All sellers, because a seller with no orders must still appear. Start from Seller, and use a LEFT JOIN.
2. How do I attach only 2020 orders? Put YEAR(o.sale_date) = 2020 in the ON clause, together with the seller match.
3. Why not in WHERE? In WHERE, it would remove the rows where o is NULL (the very sellers we want), and every inactive seller would vanish. This is the ON vs WHERE rule from the Joins notes.
4. How do I spot an empty folder? Group by seller and use HAVING COUNT(o.order_id) = 0. COUNT(column) ignores NULL, so it is 0 when no order was attached.
5. Sorting? ORDER BY s.seller_name.

Approach

Step 1: LEFT JOIN Seller with Orders Keep every seller.

Step 2: Put the 2020 condition in ON ON s.seller_id = o.seller_id AND YEAR(o.sale_date) = 2020.

Step 3: Group by seller GROUP BY s.seller_id, s.seller_name.

Step 4: Keep sellers with no attached orders HAVING COUNT(o.order_id) = 0.

Step 5: Sort ORDER BY s.seller_name.

SQL Query

Version 1: as given

SELECT s.seller_name
FROM Seller s
-- LEFT JOIN keeps every seller, even those with no matching 2020 order
LEFT JOIN Orders o
       ON s.seller_id = o.seller_id
      AND YEAR(o.sale_date) = 2020        -- the year filter is in ON, not in WHERE
GROUP BY s.seller_id, s.seller_name       -- one row per seller
HAVING COUNT(o.order_id) = 0              -- no 2020 order was attached
ORDER BY s.seller_name;

Version 2: NOT EXISTS with a date range (clear and index-friendly)

SELECT s.seller_name
FROM Seller s
WHERE NOT EXISTS (
  SELECT 1
  FROM Orders o
  WHERE o.seller_id = s.seller_id
    AND o.sale_date >= '2020-01-01'
    AND o.sale_date <  '2021-01-01'
)
ORDER BY s.seller_name;

Both versions return the same result. Version 2 reads exactly like the question: "sellers for whom there is no order in 2020", and it needs no GROUP BY. (NOT EXISTS is covered in the Subqueries notes.)

Query Explanation

• FROM Seller s LEFT JOIN Orders o — every seller appears at least once in the joined result.
• ON s.seller_id = o.seller_id AND YEAR(o.sale_date) = 2020 — an order is attached to a seller only if it is that seller's order and it is from 2020. Orders from other years are never attached.
• GROUP BY s.seller_id, s.seller_name — collects each seller's attached orders into one group. Grouping by seller_id as well as the name keeps two sellers with the same name apart.
• COUNT(o.order_id) — counts attached orders. If a seller has none, the LEFT JOIN made o.order_id NULL, and COUNT(column) ignores NULL, so the count is 0.
• HAVING COUNT(o.order_id) = 0 — keeps only sellers with zero attached 2020 orders.
• ORDER BY s.seller_name — sorts the names ascending.

Dry Run

Original tables

Seller: Daniel (1), Elizabeth (2), Frank (3). Orders: 5 rows (shown in the example).

↓

Step 1: LEFT JOIN, attaching only 2020 orders (the ON condition)

(Elizabeth's order 5 is from 2019, so it is not attached.)

↓

Step 2: GROUP BY seller and COUNT(o.order_id)

↓

Step 3: HAVING COUNT = 0, then ORDER BY seller_name

SQL Execution Flow

FROM Seller s
   |
LEFT JOIN Orders o ON same seller AND order is from 2020
   |   (every seller kept; unmatched sellers get NULL order columns)
   |
GROUP BY s.seller_id, s.seller_name
   |
COUNT(o.order_id) per seller
   |
HAVING COUNT(o.order_id) = 0
   |
SELECT seller_name
   |
ORDER BY seller_name

Time Complexity

Time Complexity: O((s + o) log (s + o)), where s is the number of sellers and o is the number of orders The join is fast with an index on Orders.seller_id. Grouping and sorting add the log factor.

Space Complexity: O(s) The database keeps one counter per seller.

Why: Version 2 (NOT EXISTS) can be cheaper because it can stop looking at a seller's orders as soon as it finds one 2020 sale, and it does not need to group.

Common Mistakes to Avoid

Mistake 1: Putting the year filter in WHERE instead of ON

Why it happens: Filters usually go in WHERE.

Wrong:

SELECT s.seller_name
FROM Seller s
LEFT JOIN Orders o ON s.seller_id = o.seller_id
WHERE YEAR(o.sale_date) = 2020
GROUP BY s.seller_id, s.seller_name
HAVING COUNT(o.order_id) = 0;

For a seller with no match, o.sale_date is NULL, so YEAR(NULL) = 2020 is not true and the row is removed. Only sellers with 2020 sales survive, so the HAVING COUNT = 0 finds nothing and the result is empty.

Correct: Put the year condition in the ON clause.

Mistake 2: Using COUNT(*) instead of COUNT(o.order_id)

Why it happens: COUNT(*) is the usual habit.

Wrong:

HAVING COUNT(*) = 0

A seller with no attached orders still has one joined row (with NULLs), so COUNT(*) is 1 and never 0. The result is empty.

Correct: COUNT(o.order_id), which ignores NULL.

Mistake 3: Filtering rows with "not in 2020" instead of asking about the seller

Why it happens: "No sales in 2020" is read as "sales that are not in 2020".

Wrong:

SELECT DISTINCT s.seller_name
FROM Seller s
JOIN Orders o ON s.seller_id = o.seller_id
WHERE YEAR(o.sale_date) <> 2020;

Elizabeth also sold in 2019, so her 2019 row passes the filter and she is wrongly listed, even though she sold in 2020. Sellers with no orders at all (like Ivy) are also lost.

Correct: Test the seller as a whole (no 2020 order exists), using LEFT JOIN with HAVING, or NOT EXISTS.

Mistake 4: Using an INNER JOIN

Why it happens: JOIN is the default word.

Result: sellers with no orders in 2020 have no match and disappear, which are exactly the ones we want.

Correct: LEFT JOIN from Seller.

Mistake 5: Grouping by seller_name only

Why it happens: It is the only selected column.

Why it hurts: Two different sellers with the same name would be merged into one group.

Correct: GROUP BY s.seller_id, s.seller_name.

Mistake 6: Forgetting ORDER BY

Why it happens: The rows may look sorted by accident.

Correct: Add ORDER BY s.seller_name.

Mistake 7: Joining the Customer table

Why it happens: It is one of the three tables given.

Why it hurts: The question does not involve customers, and an extra join can drop or repeat rows.

Correct: Use only Seller and Orders.

Mistake 8: BETWEEN on a DATETIME version of this problem

Why it happens: Habit from DATE columns.

Why it matters: Here sale_date is a DATE, so BETWEEN '2020-01-01' AND '2020-12-31' happens to work. If the column were a DATETIME, it would miss most of Dec 31. The half-open range is always safe.

Correct: >= '2020-01-01' AND < '2021-01-01'.

Interview Follow-up Questions

• "Why must the year condition be in ON and not in WHERE in the LEFT JOIN version?"
• "Why does COUNT(*) = 0 never return any seller here?"
• "Rewrite it with LEFT JOIN ... WHERE o.order_id IS NULL." (Keep the year condition in ON, then test o.order_id IS NULL in WHERE.)
• "How would you list sellers who sold in 2019 but not in 2020?"
• "Which version would you choose on a table with 100 million orders? Why?"

Similar Problems

• Customers who never placed an order (LEFT JOIN with IS NULL, in the Joins notes)
• Users who have never ordered (NOT EXISTS, in the Subqueries notes)
• Any "no activity in a period" report, such as users with no login this month

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• "Who had no activity in a period" → LEFT JOIN with the period condition in ON, then test for NULL or a zero count; or NOT EXISTS
• A condition about the joined table must not remove the left table's rows → put it in ON, not WHERE
• Counting matches after a LEFT JOIN → COUNT(right_table.column), never COUNT(*)
• Period filter on dates → half-open range, >= start AND < next_start