-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcreate_facts.sql
117 lines (110 loc) · 3.32 KB
/
create_facts.sql
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
-- ===========
-- Order Fact
-- ===========
create table dwh.fact_bike_order as
select
to_char(o.order_date, 'yyyymmdd')::int as order_date_id
,to_char(o.required_date, 'yyyymmdd')::int as requirement_date_id
,o.customer_id
,o.staff_id
,o.store_id
,oi.product_id
,o.order_id
,oi.quantity
,oi.list_price
,oi.discount
,oi.list_price * oi.quantity as order_amount
,(oi.list_price - oi.discount) * oi.quantity as discounted_order_amount
from bike_stores.orders o
join bike_stores.order_items oi
on o.order_id = oi.order_id
;
alter table dwh.fact_bike_order
add constraint f_bike_order_date_fk
foreign key (order_date_id) references dwh.dim_date(date_id)
;
alter table dwh.fact_bike_order
add constraint f_bike_order_requirement_d_date_fk
foreign key (requirement_date_id) references dwh.dim_date(date_id)
;
alter table dwh.fact_bike_order
add constraint f_bike_order_d_customer_fk
foreign key (customer_id) references dwh.dim_customer(customer_id)
;
alter table dwh.fact_bike_order
add constraint f_bike_order_d_product_fk
foreign key (product_id) references dwh.dim_product(product_id)
;
alter table dwh.fact_bike_order
add constraint f_bike_order_d_staff_fk
foreign key (staff_id) references dwh.dim_staff(staff_id)
;
alter table dwh.fact_bike_order
add constraint f_bike_order_d_store_fk
foreign key (store_id) references dwh.dim_store(store_id)
;
-- ===============
-- Shipment Fact
-- ===============
create table dwh.fact_bike_shipment as
select
to_char(o.shipped_date, 'yyyymmdd')::int as shipment_date_id
,o.customer_id
,o.staff_id
,o.store_id
,oi.product_id
,o.order_id
,oi.quantity
,oi.list_price
,oi.discount
,oi.list_price * oi.quantity as shipment_amount
,(oi.list_price - oi.discount) * oi.quantity as discounted_shipment_amount
from bike_stores.orders o
join bike_stores.order_items oi
on o.order_id = oi.order_id
where o.shipped_date is not null
;
alter table dwh.fact_bike_shipment
add constraint f_bike_shipment_d_date_fk
foreign key (shipment_date_id) references dwh.dim_date(date_id)
;
alter table dwh.fact_bike_shipment
add constraint f_bike_shipment_d_customer_fk
foreign key (customer_id) references dwh.dim_customer(customer_id)
;
alter table dwh.fact_bike_shipment
add constraint f_bike_shipment_d_product_fk
foreign key (product_id) references dwh.dim_product(product_id)
;
alter table dwh.fact_bike_shipment
add constraint f_bike_shipment_d_staff_fk
foreign key (staff_id) references dwh.dim_staff(staff_id)
;
alter table dwh.fact_bike_shipment
add constraint f_bike_shipment_d_store_fk
foreign key (store_id) references dwh.dim_store(store_id)
;
-- ============================
-- Store Stock Fact
-- ============================
create table dwh.fact_store_stock as
select
to_char('2021-06-23'::date , 'yyyymmdd')::int as date_id
,store_id
,product_id
,quantity
from
bike_stores.stocks
;
alter table dwh.fact_store_stock
add constraint f_store_stock_d_store_fk
foreign key (store_id) references dwh.dim_store(store_id)
;
alter table dwh.fact_store_stock
add constraint f_store_stock_d_product_fk
foreign key (product_id) references dwh.dim_product(product_id)
;
alter table dwh.fact_store_stock
add constraint f_store_stock_d_date_fk
foreign key (date_id) references dwh.dim_date(date_id)
;