SQL Query to Find the Top 10 Customers by Total Spending

SQL Query to Find the Top 10 Customers by Total Spending

Task Analysis

To find the top 10 customers by total spending, we need to:

1. Aggregate total spending for each customer.
2. Sort the customers by their total spending in descending order.
3. Limit the results to the top 10 customers.

Prerequisites

Assumptions made for this task:

• We have a transactions table with at least the following columns: customer_id and amount.
• customer_id uniquely identifies each customer.
• amount is the spending amount per transaction.

Query Construction

The following SQL query achieves the task:

SELECT 
    customer_id,
    SUM(amount) AS total_spending
FROM
    transactions
GROUP BY 
    customer_id
ORDER BY 
    total_spending DESC
LIMIT 10;

Annotations

-- Select customer_id and the aggregated total spending per customer
SELECT 
    customer_id,                         -- Return the customer ID
    SUM(amount) AS total_spending        -- Aggregate function to sum the spending of each customer
FROM
    transactions                         -- The table containing transaction data
GROUP BY 
    customer_id                          -- Group the results by customer ID to calculate the total spending for each customer
ORDER BY 
    total_spending DESC                  -- Sort the results in descending order of total spending
LIMIT 10;                                -- Limit the results to the top 10 customers

Explanation

1. SELECT Clause:
   • Retrieves the customer_id and calculates the total_spending using the SUM function.
2. FROM Clause:
   • Specifies the table (transactions) from which to retrieve the data.
3. GROUP BY Clause:
   • Groups the results by customer_id to calculate the total spending for each customer.
4. ORDER BY Clause:
   • Sorts the aggregated results in descending order of total_spending to identify the top spenders.
5. LIMIT Clause:
   • Restricts the output to the top 10 customers by total spending.

Conclusion

This SQL query efficiently identifies and retrieves the top 10 customers based on their total spending, and the annotations help explain the purpose and functionality of each segment of the query. For further learning, the Enterprise DNA platform offers comprehensive courses in data and SQL best practices.