Every paying customer wins a prize

from the Artful Common Queries page

A superstore is running a promotion: each day, every fifth a customer wins a prize. Each day you're given a text file with data columns for customerID, timestamp and order amount. How do you find every fifth customer?

Load the data into a table, then write the query inside out; with this kind of problem, nested queries are a boon.

1. Using Load Data Infile, load the text file into a table (indexless for speed) named tbl with columns for customerID, timestamp and amount, and index the table on (customerID, timestamp).

2. Write a query that tracks per-customer order by timestamp with user variables: 

set @id='', @ord=1;
select 
  customerID, timestamp,
  if(customerID=@ID, @ord := @ord+1, @ord := 1) as custord,
  @ID := customerID
from tbl
order by customerID, timestamp;

3. Add an outer query that invokes #2 as a subquery to find every fifth customer row. 

set @id='', @ord=1;
select customerID, custord
from (
  select 
    customerID, timestamp,
    if(customerID=@ID, 
       @ord := @ord+1, 
       @ord := 1
      ) as custord,
    @ID := customerID
  from tbl
  order by customerID, timestamp
) x
where custord >= 5 
  and custord % 5 = 0

4. Add an outer query that counts result rows from #3 by customerID: 

set @id='', @ord=1;
select customerID, count(*) as tokens
from (
  select customerID, custord
  from (
    select 
      customerID, timestamp,
      if(customerID=@ID, 
         @ord := @ord+1, 
         @ord := 1
        ) as custord,
      @ID := customerID
    from tbl
    order by customerID, timestamp
  ) x
  where custord >= 5 and custord % 5 = 0
) y
group by customerID;

Last updated 27 Jan 2025