Sum of payments in date range, sum tested against ladder of 9 levels, adding to a count for each level

I have a report that provides a count of customers with payments in a date range, where the sum of each customer's payment amounts is tested against a ladder of nine giving levels. So that's nine counts of customers, broken out by specified amount totals. So, add up a customer's payments in the date range, then test that sum against nine amount levels, then increment the count for their qualified level. New to SQL.

Still figuring out where to start with such a query

Solution

To start with, you will need to identify the two tables that contain the relevant data: the customer table and the payment table. The customer table will contain the customer IDs and any other relevant customer information, such as their email address. The payment table will contain the payment amounts and the dates on which the payments were made.

Once you have identified the two tables, you will need to write a SQL query to join the two tables on the customer ID column. This will allow you to group the payment amounts by customer ID and calculate the total payment amount for each customer.

Here is a basic SQL query that you can use to get started:

SELECT customer_id, SUM(payment_amount) AS total_payment_amount
FROM customer
JOIN payment ON customer.customer_id = payment.customer_id
WHERE payment_date BETWEEN '2023-10-01' AND '2023-10-13'
GROUP BY customer_id;

This query will return a table with two columns: customer_id and total_payment_amount. The customer_id column will contain the unique customer IDs and the total_payment_amount column will contain the total payment amount for each customer.

Next, you will need to add a CASE statement to the query to test the total payment amount for each customer against the nine amount levels. The CASE statement will return a number from 1 to 9, depending on which amount level the customer's total payment amount falls into.

Here is an example of how to add a CASE statement to the query:

SELECT customer_id, SUM(payment_amount) AS total_payment_amount,
CASE
    WHEN total_payment_amount < 100 THEN 1
    WHEN total_payment_amount BETWEEN 100 AND 200 THEN 2
    WHEN total_payment_amount BETWEEN 200 AND 300 THEN 3
    ...
    WHEN total_payment_amount >= 900 THEN 9
END AS payment_level
FROM customer
JOIN payment ON customer.customer_id = payment.customer_id
WHERE payment_date BETWEEN '2023-10-01' AND '2023-10-13'
GROUP BY customer_id;

The payment_level column in the output of this query will contain a number from 1 to 9, depending on which amount level the customer's total payment amount falls into.

Finally, you will need to add another CASE statement to the query to count the number of customers in each payment level. The CASE statement will return a number from 1 to 9, depending on the value of the payment_level column.

Here is an example of how to add another CASE statement to the query to count the number of customers in each payment level:

SELECT customer_id, SUM(payment_amount) AS total_payment_amount,
CASE
    WHEN total_payment_amount < 100 THEN 1
    WHEN total_payment_amount BETWEEN 100 AND 200 THEN 2
    WHEN total_payment_amount BETWEEN 200 AND 300 THEN 3
    ...
    WHEN total_payment_amount >= 900 THEN 9
END AS payment_level,
CASE
    WHEN payment_level = 1 THEN COUNT(*)
    WHEN payment_level = 2 THEN COUNT(*)
    WHEN payment_level = 3 THEN COUNT(*)
    ...
    WHEN payment_level = 9 THEN COUNT(*)
END AS customer_count
FROM customer
JOIN payment ON customer.customer_id = payment.customer_id
WHERE payment_date BETWEEN '2023-10-01' AND '2023-10-13'
GROUP BY customer_level;

The customer_count column in the output of this query will contain the number of customers in each payment level.

You can then modify the query to include any other relevant columns, such as the customer's email address or the payment date range.