Skip to content
Advertisement

Lead window function in mysql to find sales

Given this table. I would like to know for each day how many different customers made a sale on date t and and t+1.

The result for date 2021-06-30 is 2 because customer 1 and 3 made a sale in t and t+1.

Advertisement

Answer

Use LEAD() window function for each distinct combination of date and customer to create a flag which will be 1 if the customer is present in both days or 0 if not and aggregate:

See the demo.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement