We have two tables: "customer" and "sales"
"customer"
customer_id | customer_name |
1 | Customer A |
2 | Customer B |
3 | Customer C |
4 | Customer D |
5 | Customer E |
"sales"
transaction_id | customer_id | product_name | category_name | amount |
1 | 1 | Product A | Category 1 | 10 |
2 | 2 | Product B | Category 1 | 15 |
3 | 3 | Product C | Category 2 | 20 |
4 | 4 | Product D | Category 2 | 12 |
5 | 5 | Product E | Category 3 | 7 |
6 | 1 | Product F | Category 3 | 5 |
7 | 2 | Product G | Category 4 | 18 |
8 | 3 | Product H | Category 4 | 22 |
9 | 4 | Product I | Category 5 | 12 |
10 | 5 | Product J | Category 5 | 8 |
Write a CTE expression to get the total_salesof each customer from a table of sales transactions.
After that, join this CTE with a table of customer data to get the customer_name and total_sales, arranged by the total sales in descending order.