File size: 1,209 Bytes
12eff8e | 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 | -- Customer transactions staging table
CREATE TABLE staging.customer_transactions (
customer_id INT NOT NULL,
order_id INT NOT NULL,
order_date DATE NOT NULL,
order_amount DECIMAL(18,2),
discount_amount DECIMAL(18,2),
payment_method VARCHAR(50),
created_at TIMESTAMP DEFAULT SYSDATE
)
SEGMENTED BY HASH(customer_id) ALL NODES
ORDER BY customer_id, order_date;
-- Customers dimension
CREATE TABLE staging.customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(200),
email VARCHAR(200),
registration_date DATE,
country_code VARCHAR(3),
is_active INT DEFAULT 1
)
SEGMENTED BY HASH(customer_id) ALL NODES;
-- Customer daily fact table
CREATE TABLE analytics.customer_daily (
customer_id INT NOT NULL,
activity_date DATE NOT NULL,
order_count INT,
total_spend DECIMAL(18,2),
last_order_date DATE,
avg_order_value DECIMAL(18,2),
customer_segment VARCHAR(20),
activity_status VARCHAR(20)
)
SEGMENTED BY HASH(customer_id) ALL NODES
ORDER BY activity_date, customer_id;
|