If no customers visit a restaurant for more than 3 days, then that restaurant will serve a new type of food starting when the next customer visits.
All restaurants serve food in the same order. Figure out which customer ate which food.
Sample Data:
Restaurant:
| restaurant_id | customer_id | visit_date |
|---|
| 1 | 1 | 2020-01-01 |
| 2 | 1 | 2020-01-01 |
| 1 | 2 | 2020-01-03 |
| 3 | 1 | 2020-01-04 |
| 2 | 2 | 2020-01-14 |
| 3 | 1 | 2020-01-11 |
| 2 | 3 | 2020-01-14 |
Food:
| food_id | food_name |
|---|
| 1 | Spaghetti |
| 2 | Chicken and Rice |
| 3 | Tacos |
Desired:
| Restaurant | Customer | Visit | Food |
|---|
| 1 | 1 | 2020-01-01 | Spaghetti |
| 2 | 1 | 2020-01-01 | Spaghetti |
| 1 | 2 | 2020-01-03 | Spaghetti |
| 3 | 1 | 2020-01-04 | Spaghetti |
| 2 | 2 | 2020-01-10 | Chicken and Rice |
| 3 | 1 | 2020-01-11 | Chicken and Rice |
| 2 | 3 | 2020-01-14 | Tacos |
Not sure whether to use CTE or row_number() over (partition by )