Bird
0
0

Given this dbt model SQL code using CTEs, what will be the final output table's column total_sales?

medium📝 Predict Output Q13 of 15
dbt - Advanced Patterns
Given this dbt model SQL code using CTEs, what will be the final output table's column total_sales?
WITH sales AS (
  SELECT customer_id, amount
  FROM {{ ref('raw_sales') }}
),
filtered_sales AS (
  SELECT customer_id, amount
  FROM sales
  WHERE amount > 100
)
SELECT customer_id, SUM(amount) AS total_sales
FROM filtered_sales
GROUP BY customer_id
ASum of all sales amounts per customer including amounts <= 100
BSum of sales amounts per customer where each amount is greater than 100
CCount of sales transactions per customer
DAverage sales amount per customer
Step-by-Step Solution
Solution:
  1. Step 1: Analyze the filtering condition in filtered_sales CTE

    The CTE filters sales to only include rows where amount > 100.
  2. Step 2: Understand the aggregation in the final SELECT

    The final query sums the filtered amounts per customer, so total_sales is sum of amounts > 100 per customer.
  3. Final Answer:

    Sum of sales amounts per customer where each amount is greater than 100 -> Option B
  4. Quick Check:

    Filter then sum = D [OK]
Quick Trick: Filter first, then aggregate sums [OK]
Common Mistakes:
MISTAKES
  • Ignoring the WHERE filter on amount
  • Confusing sum with count or average
  • Assuming all sales are included

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More dbt Quizzes