## 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 2020

Return to the Artful Common Queries page