SQL Series #2: Calculating Loyalty Points with INTERVAL

Problem Statement

In the first week after joining (including the join date), customers earn 2x points on all items, not just sushi. Normally, each $1 spent equates to 10 points, with just sushi earning a 2x multiplier. How many points do members accumulate by the end of January 2021?

The following are the input tables:

sales

members

menu

Expected Output

Approach

The solution utilizes INTERVAL concept. How it Works:

• Add or Subtract Time: You can use INTERVAL to add or subtract a specified amount of time to a date. For example, join_date + INTERVAL '7 days' adds 7 days to the join_date.
• Dynamic Date Calculations: It enables you to set dynamic conditions based on time. For instance, determining if an order was placed within the first 7 days after a customer joined a loyalty program.

Here are the steps to reach the solution:

?? Step 1: Calculate Points for Each Order

Each customer's order is evaluated to calculate points:

• In the first 7 days after a customer joins, they earn double points for every dollar spent.
• After the first week, they earn double points for sushi orders and standard points for other products.

?? Step 2: Sum Up Points Per Customer

The points from all orders are then summed up for each customer, giving us their total points earned over the specified period.

?? Step 3: Generate the Final Output

The final result is a table showing each customer’s total loyalty points based on their order history, considering the special point rules.

Here is how the points are calculated:

Customer 1:

2021-01-05 (Join date + 4 days): 'sushi' → 20 × 2 × 10 = 400 20×2×10 = 400 points

2021-01-10: 'burger' → 15 × 10 = 150 15×10 = 150 points

Customer 2:

2021-01-15 (Join date + 5 days): 'pasta' → 12 × 2 × 10 = 240 12×2×10 = 240 points

2021-01-20: 'sushi' → 20 × 2 × 10 = 400 20×2×10 = 400 points

Full Solution Code