✓ Consider the table structure
sales: This table captures every transaction made by customers. It includes:
• sale_id: Unique identifier for each sale.
• customer_id: Identifier linking the sale to a customer.
• product_id: Identifier for the product sold.
• sale_date: The date when the sale was made.
• amount: The total amount of money spent in the sale.
• customers: This table contains information about each customer, including:
• customer_id: Unique identifier for each customer.
• customer_name: Name of the customer.
• customer_type: Type of customer (‘Regular’, ‘VIP’).
Questions:
• Top 5 customers by total sales amount:
• Write a query that joins the sales and customers tables to aggregate
the total sales amount for each customer. Then, use the ORDER BY
clause to sort the customers by the total amount spent, and limit the
result to the top 5 customers.
• This helps identify the most valuable customers for the business.
• Identify inactive customers:
• You need to identify customers who have not made any purchases in
the last year. This involves filtering the sales table for transactions
within the last year and then finding customers who do not appear
in that list.
• This is crucial for re-engagement strategies, targeting customers
who might be slipping away.
• Average sale amount for VIP customers:
• Calculate the average sales amount for customers categorized as
VIPs, grouped by month. This will involve filtering the customers by
their type and then aggregating the sales data every month.
• This metric helps in understanding the spending behavior of VIP
customers, which could influence loyalty programs.
2. You are part of the analytics team responsible for monitoring product performance. Your
task is to generate a report on product sales and identify trends or anomalies. The data
you need is spread across two tables: products and sales.
Tables:
products: This table contains details about each product.
product_id: Unique identifier for each product.
product_name: Name of the product.
category: The category to which the product belongs.
price: Price of the product.
sales: This table records sales transactions.
sale_id: Unique identifier for each sale.
product_id: Identifier linking the sale to a product.
quantity_sold: Number of units sold in each transaction.
sale_date: Date of the sale.
Task explanation:
✓ Total revenue by product category:
1. Use a CTE to calculate the total revenue generated by each
product category. The CTE will allow you to segment your data and
then aggregate the revenue per category.
2. This is useful for understanding which product categories are
driving the most revenue.
✓ Products exceeding average sales:
a. Write a query to find products whose total sales exceed the average sales for
their category. This may involve using subqueries to calculate the average and
then filtering the products that surpass this threshold.
b. Identifying such products can help in focusing marketing efforts on high
performers.
✓ Identify unsold products:
a. Determine which products have not been sold in the last 3 months. This could
involve subqueries to filter out products that have recorded sales recently.
b. This helps in managing inventory and reducing holding costs for unsold products.
