Level 6 · Costly returns that were our fault

This ticket combines three filters you have already used: a list of allowed values, a number threshold and a date range. The table is new: returns, one row per returned order line, with the refund paid out.

Three conditions, one WHERE

Every condition Rachel listed must be true at once, so they are joined with AND. The two reasons go into an IN list:

SELECT reason, refund_amount, returned_on
FROM returns
WHERE reason IN ('damaged', 'wrong_item')
  AND refund_amount > 200
  AND returned_on >= '2025-01-01'
  AND returned_on < '2026-01-01';

That returns 69 rows. Across all three years there are 477 damaged and 255 wrong-item returns, so the threshold and the year cut the list down to the ones worth a meeting.

returned_on is a DATE, so <= '2025-12-31' would also work here. The half-open range (>= the first day, < the first day of the next period) is still the habit to keep: it works unchanged when the column is a timestamp, where <= '2025-12-31' silently drops everything after midnight on December 31.

Count as you go

A quick way to trust a filter is to add one condition at a time and watch the row count. Start with the reasons, then add the threshold, then the year. Each step should shrink the result, and none should empty it. If adding a condition changes nothing, it is probably not doing what you think.

Common mistakes

• Mixing OR with AND without parentheses. reason = 'damaged' OR reason = 'wrong_item' AND refund_amount > 200 keeps every damaged return, cheap ones included. IN avoids the problem.
• Using >= when the ticket says “more than”. More than $200 is > 200; a refund of exactly $200.00 does not qualify.
• Typing reasons the way people say them. The stored values are lowercase with underscores, like 'wrong_item'. Check with SELECT DISTINCT reason FROM returns before you filter on text.

In SQL Server

The WHERE is identical, and date strings in the '2025-01-01' form compare the same way. To filter by year you will also see YEAR(returned_on) = 2025 there. It reads well, but wrapping the column in a function stops the database from using an index on it, so the half-open range is still the better habit on large tables:

WHERE reason IN ('damaged', 'wrong_item')
  AND refund_amount > 200
  AND returned_on >= '2025-01-01' AND returned_on < '2026-01-01';