-
Notifications
You must be signed in to change notification settings - Fork 0
/
revenue_timeseries.yaml
201 lines (198 loc) · 6.87 KB
/
revenue_timeseries.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
name: Revenue
tables:
- name: daily_revenue
description: Daily total revenue, aligned with daily "Cost of Goods Sold" (COGS), and forecasted revenue.
base_table:
database: cortex_analyst_demo
schema: revenue_timeseries
table: daily_revenue
time_dimensions:
- name: date
expr: date
description: date with measures of revenue, COGS, and forecasted revenue.
unique: true
data_type: date
measures:
- name: daily_revenue
expr: revenue
description: total revenue for the given day
synonyms: ["sales", "income"]
default_aggregation: sum
data_type: number
- name: daily_cogs
expr: cogs
description: total cost of goods sold for the given day
synonyms: ["cost", "expenditures"]
default_aggregation: sum
data_type: number
- name: daily_forecasted_revenue
expr: forecasted_revenue
description: total forecasted revenue for a given day
synonyms: ["forecasted sales", "forecasted income"]
default_aggregation: sum
data_type: number
- name: daily_profit
description: profit is the difference between revenue and expenses.
expr: revenue - cogs
data_type: number
- name: daily_forecast_abs_error
synonyms:
- absolute error
- L1
description: absolute error between forecasted and actual revenue
expr: abs(forecasted_revenue - revenue)
data_type: number
default_aggregation: avg
- name: daily_revenue_by_product_line
description: Daily revenue sliced by product line, aligned with daily "Cost of Goods Sold" (COGS), and forecasted revenue.
base_table:
database: cortex_analyst_demo
schema: revenue_timeseries
table: daily_revenue_by_product
time_dimensions:
- name: date
expr: date
description: date with measures of revenue, COGS, and forecasted revenue for each product line
unique: false
data_type: date
dimensions:
- name: product_line
expr: product_line
description: product line associated with it's own slice of revenue
unique: false
data_type: varchar
sample_values:
- Electronics
- Clothing
- Home Appliances
- Toys
- Books
measures:
- name: daily_revenue_per_product_line
expr: revenue
description: revenue associated with a given product line for the given day
synonyms: ["sales", "income"]
default_aggregation: sum
data_type: number
- name: daily_cogs_per_product_line
expr: cogs
description: cost of goods sold associated with a given product line for the given day
synonyms: ["cost", "expenditures"]
default_aggregation: sum
data_type: number
- name: daily_forecasted_revenue_per_product_line
expr: forecasted_revenue
description: total forecasted revenue associated with a given product line for the given day
synonyms: ["forecasted sales", "forecasted income"]
default_aggregation: sum
data_type: number
- name: daily_profit_per_product_line
description: profit is the difference between revenue and expenses.
expr: revenue - cogs
data_type: number
- name: daily_forecast_abs_error_per_product_line
synonyms:
- absolute error
- L1
description: absolute error between forecasted and actual revenue
expr: abs(forecasted_revenue - revenue)
data_type: number
default_aggregation: avg
- name: daily_revenue_by_region
description: Daily revenue sliced by region, aligned with daily "Cost of Goods Sold" (COGS), and forecasted revenue.
base_table:
database: cortex_analyst_demo
schema: revenue_timeseries
table: daily_revenue_by_region
time_dimensions:
- name: date
expr: date
description: date with measures of revenue, COGS, and forecasted revenue for each region
unique: false
data_type: date
dimensions:
- name: sales_region
expr: sales_region
description: region associated with it's own slice of revenue
unique: false
data_type: varchar
sample_values:
- North America
- Europe
- Asia
- South America
- Africa
measures:
- name: daily_revenue_per_sales_region
expr: revenue
description: revenue associated with a given region for the given day
synonyms: ["sales", "income"]
default_aggregation: sum
data_type: number
- name: daily_cogs_per_sales_region
expr: cogs
description: cost of goods sold associated with a given region for the given day
synonyms: ["cost", "expenditures"]
default_aggregation: sum
data_type: number
- name: daily_forecasted_revenue_per_sales_region
expr: forecasted_revenue
description: total forecasted revenue associated with a given region for the given day
synonyms: ["forecasted sales", "forecasted income"]
default_aggregation: sum
data_type: number
- name: daily_profit_per_sales_region
description: profit is the difference between revenue and expenses.
expr: revenue - cogs
data_type: number
- name: daily_forecast_abs_error_per_sales_region
synonyms:
- absolute error
- L1
description: absolute error between forecasted and actual revenue
expr: abs(forecasted_revenue - revenue)
data_type: number
default_aggregation: avg
verified_queries:
# For eval sample nlimtiaco_sc_3__0
- name: "daily cumulative expenses in 2023 dec"
question: "daily cumulative expenses in 2023 dec"
verified_at: 1714752498
verified_by: renee
sql: "
SELECT
date,
SUM(daily_cogs) OVER (
ORDER BY
date ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_cogs
FROM
__daily_revenue
WHERE
date BETWEEN '2023-12-01'
AND '2023-12-31'
ORDER BY
date DESC;
"
# For eval sample nlimtiaco_sc_6__0
- name: "lowest revenue each month"
question: For each month, what was the lowest daily revenue and on what date did
that lowest revenue occur?
sql: "WITH monthly_min_revenue AS (
SELECT
DATE_TRUNC('MONTH', date) AS month,
MIN(daily_revenue) AS min_revenue
FROM __daily_revenue
GROUP BY
DATE_TRUNC('MONTH', date)
)
SELECT
mmr.month,
mmr.min_revenue,
dr.date AS min_revenue_date
FROM monthly_min_revenue AS mmr JOIN __daily_revenue AS dr
ON mmr.month = DATE_TRUNC('MONTH', dr.date) AND mmr.min_revenue = dr.daily_revenue
ORDER BY mmr.month DESC NULLS LAST"
verified_at: 1715187400
verified_by: renee