Problem Description

The table records stores the daily orders of every customer. Its fields are row_id (row number), customer_id (customer number), date and order_count (number of orders placed that day). The records of each customer are sorted by date in ascending order, and row_id is in the same order as date. For every customer, find the row_id of the record where order_count first became 0; customers that never had a zero-order day must also appear in the result, with an empty row_id.

Source Data

Expected Result

One row per customer, row_id being the record number of that customer first zero-order day, empty if there is none:

Customer 1 has only one zero-order day (2024-01-04, row_id=4), so the result is 4.

Customer 3 has two zero-order days (row_id=11 on 2024-01-01 and row_id=14 on 2024-01-04). The earlier one has to be taken, which is row_id=11, so the later 14 must not appear in the result.

Customer 2 has no zero-order day in these five days, yet still gets a row in the result, with an empty row_id.

SQLazy Step-by-Step Implementation

The problem has to satisfy two things at once: take the earliest zero-order record, and keep the customers that have no zero-order record at all. The two things are handled separately: first take a complete customer list from the source table, then group the source table by customer and take the smallest row_id among the zero-order records only, which gives a lookup table from customer to zero-order record, and finally left join that table to the customer list. The left join keeps the customers that found no match in the result, and the unmatched position is empty, which is exactly the null the problem asks for.

[Click to run this example online]

The steps are explained below.

Step 1: Take the customer number column

derive customer_id

Keep only the customer_id column, preparing the way for deduplication in the next step.

Step 2: Get the complete customer list
distinct customer_id
Deduplicate by customer_id to get a complete list of one row per customer, 3 rows in total. This list is the safety net that keeps customers with no zero-order day in the result.

Step 3: Take the earliest zero-order record of each customer
summarize min row_id condition (order_count=0) as row_id; group customer_id
Group by customer_id, write the aggregation min before the aggregated item row_id, and name the result row_id. The condition parameter filters the records of the group first, letting only the records with order_count=0 take part in the aggregation, so what is computed is the smallest row_id among the zero-order records; when a group has no zero-order record at all (such as customer 2) the result is empty.
Taking the smallest row_id is the same as taking the earliest record, which relies on row_id being in the same order as date as stated in the problem description; when the data is not in that order, the smallest date has to be taken instead.
The parameter order is aggregation, aggregated item, condition and naming; writing the condition before the aggregation is wrong.

Step 4: Left join the customer list with the zero-order records
join customer_id; with t_zeros; customer_id; take row_id; left
Take the customer list t_all as the base table, join t_zeros by customer_id and attach its row_id. The left join keeps every customer in the list, and the unmatched row_id is empty; an inner join would drop customer 2.

Generated SQL

After confirming the logic of the above 4 steps, the SQLazy compiler automatically generates native SQL (MySQL syntax used here):

WITH t_ids AS (
    SELECT customer_id
    FROM records
  ),
  t_all AS (
    SELECT customer_id
    FROM t_ids
    GROUP BY customer_id
  ),
  t_zeros AS (
    SELECT customer_id
      , MIN(CASE
        WHEN order_count = 0 THEN row_id
        ELSE NULL
      END) AS row_id
    FROM records
    GROUP BY customer_id
  ),
  t_result AS (
    SELECT t_all.customer_id, t_zeros.row_id
    FROM t_all
      LEFT JOIN t_zeros ON t_all.customer_id = t_zeros.customer_id
  )
SELECT customer_id, row_id
FROM t_result

SQLazy lets you describe logic in business language instead of writing nested SQL queries. Taking the earliest record and keeping every customer are split into two steps, each in charge of one thing: summarize takes the smallest row_id among the zero-order records, and the left join keeps all customers.

The summarize action filters before it aggregates: the condition parameter screens the records first, so only the zero-order records are aggregated, and a group without any zero-order record comes out empty. The aggregation algorithm goes before the aggregated item and the condition right after it, so the parameter order is the order in which the business states it.

The join type is a literal translation of the business wording: write a left join to keep all customers, or an inner join to keep only the customers that match.

Official Links

SQLazy Online Experience: sqlazy.com (free, no registration required)

SQLazy Repository: github.com/SPLWare/SQLazy