4. Daily Revenue for a Reporting Window
Difficulty: Easy · Topic: Jinja, vars & packages
Finance reruns the daily revenue report for different periods by passing dates on the command line: dbt run --vars '{start_date: "2024-03-01", end_date: "2024-03-31"}'.
Write daily_revenue from {{ ref('fct_orders') }}: one row per day that has orders between var('start_date') and var('end_date'), both inclusive, with order_date, orders (count) and revenue (sum of amount).
The hidden tests pass different dates, so they must come from the vars.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ ref('fct_orders') }}
| order_id | ordered_on | amount |
|---|---|---|
| 1 | 2024-02-29 | 10 |
| 2 | 2024-03-01 | 20 |
| 3 | 2024-03-01 | 5.5 |
| 4 | 2024-03-03 | 7.25 |
| 5 | 2024-03-04 | 100 |
Expected output
| order_date | orders | revenue |
|---|---|---|
| 2024-03-01 | 2 | 25.5 |
| 2024-03-03 | 1 | 7.25 |
Hints
Hint 1
{{ var('start_date') }} renders the raw text 2024-03-01, without quotes. Put it inside a SQL string: '{{ var("start_date") }}'.Hint 2
between includes both ends. With < instead, the last day of the window disappears.More dbt problems
- Pivot Payments with a Jinja Loop · Medium
- Smaller Builds in Development · Medium
- Surrogate Key for Order Lines · Medium