runtimetask History, SCD & as-of · hard
Customer segment at order time
For sale orders placed in November 2011 by identified customers, return order_id, customer_id and segment: the customer’s segment in the customer_history version valid on the order date.
what to return
- Columns
order_idcustomer_idsegmentin this order- Rows
- 333 rows · any order
- Skill
- Attribute each event to the version valid at its time. Refresher: Latest-state reporting versus SCD Type 2 →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result333 rows · any order
| order_id | customer_id | segment |
|---|---|---|
| 6139 | 17733 | occasional |
| 6140 | 17389 | key_account |
| 6141 | 17166 | occasional |
first 3 of 333 rows
data you can query
Retail dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
orders6 columns · 6,722 rows
| order_idnumber · unique id | invoice_notext | customer_idnumber · links to customers | ordered_atdate & time | countrytext | order_typetext |
|---|---|---|---|---|---|
| 1 | 489438 | 18102 | 2009-12-01 09:24:00 | United Kingdom | sale |
| 2 | 489517 | 16329 | 2009-12-01 11:34:00 | United Kingdom | sale |
| 3 | C489528 | 16714 | 2009-12-01 11:50:00 | United Kingdom | cancellation |
| 4 | 489529 | 17984 | 2009-12-01 11:51:00 | United Kingdom | sale |
| 5 | 489532 | 13394 | 2009-12-01 11:58:00 | United Kingdom | sale |
| 6 | 489540 | 12913 | 2009-12-01 12:18:00 | United Kingdom | sale |
- order_idnumber · unique id
- 1
- invoice_notext
- 489438
- customer_idnumber · links to customers
- 18102
- ordered_atdate & time
- 2009-12-01 09:24:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 2
- invoice_notext
- 489517
- customer_idnumber · links to customers
- 16329
- ordered_atdate & time
- 2009-12-01 11:34:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 3
- invoice_notext
- C489528
- customer_idnumber · links to customers
- 16714
- ordered_atdate & time
- 2009-12-01 11:50:00
- countrytext
- United Kingdom
- order_typetext
- cancellation
- order_idnumber · unique id
- 4
- invoice_notext
- 489529
- customer_idnumber · links to customers
- 17984
- ordered_atdate & time
- 2009-12-01 11:51:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 5
- invoice_notext
- 489532
- customer_idnumber · links to customers
- 13394
- ordered_atdate & time
- 2009-12-01 11:58:00
- countrytext
- United Kingdom
- order_typetext
- sale
- order_idnumber · unique id
- 6
- invoice_notext
- 489540
- customer_idnumber · links to customers
- 12913
- ordered_atdate & time
- 2009-12-01 12:18:00
- countrytext
- United Kingdom
- order_typetext
- sale
first 6 of 6,722 rows · your queries use all of them
customer_history7 columns · 1,685 rows
| history_idnumber · unique id | customer_idnumber · links to customers | segmenttext | countrytext | valid_fromdate | valid_todate | is_currentnumber |
|---|---|---|---|---|---|---|
| 1 | 12347 | occasional | Iceland | 2010-10-31 | 2010-11-01 | 0 |
| 2 | 12347 | regular | Iceland | 2010-11-01 | NULL | 1 |
| 3 | 12353 | occasional | Bahrain | 2010-10-27 | NULL | 1 |
| 4 | 12359 | occasional | Cyprus | 2009-12-05 | 2010-01-01 | 0 |
| 5 | 12359 | regular | Cyprus | 2010-01-01 | 2011-11-01 | 0 |
| 6 | 12359 | key_account | Cyprus | 2011-11-01 | NULL | 1 |
- history_idnumber · unique id
- 1
- customer_idnumber · links to customers
- 12347
- segmenttext
- occasional
- countrytext
- Iceland
- valid_fromdate
- 2010-10-31
- valid_todate
- 2010-11-01
- is_currentnumber
- 0
- history_idnumber · unique id
- 2
- customer_idnumber · links to customers
- 12347
- segmenttext
- regular
- countrytext
- Iceland
- valid_fromdate
- 2010-11-01
- valid_todate
- NULL
- is_currentnumber
- 1
- history_idnumber · unique id
- 3
- customer_idnumber · links to customers
- 12353
- segmenttext
- occasional
- countrytext
- Bahrain
- valid_fromdate
- 2010-10-27
- valid_todate
- NULL
- is_currentnumber
- 1
- history_idnumber · unique id
- 4
- customer_idnumber · links to customers
- 12359
- segmenttext
- occasional
- countrytext
- Cyprus
- valid_fromdate
- 2009-12-05
- valid_todate
- 2010-01-01
- is_currentnumber
- 0
- history_idnumber · unique id
- 5
- customer_idnumber · links to customers
- 12359
- segmenttext
- regular
- countrytext
- Cyprus
- valid_fromdate
- 2010-01-01
- valid_todate
- 2011-11-01
- is_currentnumber
- 0
- history_idnumber · unique id
- 6
- customer_idnumber · links to customers
- 12359
- segmenttext
- key_account
- countrytext
- Cyprus
- valid_fromdate
- 2011-11-01
- valid_todate
- NULL
- is_currentnumber
- 1
first 6 of 1,685 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.