ASSIGNMENT.md

DBT Assignment

WE're going to extend the DBT Marts and add additional analytic structures to the pipeline. Below is a single example followed by 4 exercises.

1. Assignment 1: Create a "Customer Lifetime Value" Mart Objective: Create a mart that calculates the lifetime value of each customer based on their total spend in the system.

Instructions:

Create a new model called customer_lifetime_value.sql under the models/marts/sales_reporting/ folder. In this model, you will calculate the total revenue per customer. Use the staging table stg_sales to sum up the total_price for each customer (customer_id). The final mart should include the following columns: customer_id total_orders: the total number of orders placed by the customer. total_revenue: the total amount spent by the customer. avg_order_value: the average value of a customer's order (total_revenue / total_orders). SQL Example:

Copy code
WITH customer_revenue AS (
    SELECT
        customer_id,
        COUNT(order_id) AS total_orders,
        SUM(total_price) AS total_revenue,
        AVG(total_price) AS avg_order_value
    FROM {{ ref('stg_sales') }}
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_orders,
    total_revenue,
    avg_order_value
FROM customer_revenue
ORDER BY total_revenue DESC;

2. Assignment 2: Create a "Product Category Performance" Mart Objective: Create a mart that aggregates product sales by product category.

Instructions:

Create a new model called category_performance.sql under the models/marts/sales_reporting/ folder. Use the stg_products and stg_sales tables to create this mart. Join stg_sales with stg_products to group products by their category. Calculate the following fields: category: the product category. total_sales: the total number of units sold per category. total_revenue: the total revenue generated per category. avg_price: the average price of products sold in that category.

3. Assignment 3: Create a "Monthly Revenue and Orders" Mart Objective: Create a mart that aggregates monthly revenue and the number of orders placed.

Instructions:

Create a new model called monthly_revenue_orders.sql under the models/marts/sales_reporting/ folder. Use the stg_sales table to calculate revenue and order counts by month. Extract the month from the order_date and group by it. The mart should have the following columns: month: the month of the year (e.g., '2024-09'). total_orders: the total number of orders placed in that month. total_revenue: the total revenue generated in that month.

4. Assignment 4: Create a "Customer Order Frequency" Mart Objective: Create a mart that calculates how frequently each customer places an order.

Instructions:

Create a new model called customer_order_frequency.sql under the models/marts/sales_reporting/ folder. Use the stg_sales table to calculate the average number of days between a customer's orders. The mart should include: customer_id total_orders: the total number of orders placed. first_order: the date of the first order. last_order: the date of the last order. avg_days_between_orders: the average time (in days) between each order for that customer.

5. Assignment 5: Create a "Top 10 Customers by Revenue" Mart Objective: Create a mart that lists the top 10 customers by total revenue generated.

Instructions:

Create a new model called top_10_customers_by_revenue.sql under the models/marts/sales_reporting/ folder. Use the stg_sales table to calculate the total revenue generated by each customer. Rank the customers by their total revenue and limit the result to the top 10. The mart should include: customer_id total_orders: the total number of orders placed by the customer. total_revenue: the total revenue generated by the customer.