26 lines
1.3 KiB
SQL
26 lines
1.3 KiB
SQL
CREATE TABLE IF NOT EXISTS orders (
|
|
order_id INTEGER PRIMARY KEY NOT NULL CHECK (typeof(order_id) = 'integer' AND order_id > 0),
|
|
order_date TEXT NOT NULL CHECK (
|
|
length(order_date) = 10 AND order_date >= '0001-01-01'
|
|
AND order_date IS date(order_date, '+0 days')
|
|
),
|
|
category TEXT NOT NULL CHECK (typeof(category) = 'text' AND length(trim(category)) > 0),
|
|
quantity INTEGER NOT NULL CHECK (typeof(quantity) = 'integer' AND quantity > 0),
|
|
unit_price_kopecks INTEGER NOT NULL CHECK (typeof(unit_price_kopecks) = 'integer' AND unit_price_kopecks > 0),
|
|
revenue_kopecks INTEGER NOT NULL CHECK (
|
|
typeof(revenue_kopecks) = 'integer' AND revenue_kopecks > 0
|
|
AND revenue_kopecks = quantity * unit_price_kopecks
|
|
)
|
|
) WITHOUT ROWID;
|
|
|
|
CREATE TABLE IF NOT EXISTS daily_category_revenue (
|
|
order_date TEXT NOT NULL CHECK (
|
|
length(order_date) = 10 AND order_date >= '0001-01-01'
|
|
AND order_date IS date(order_date, '+0 days')
|
|
),
|
|
category TEXT NOT NULL CHECK (typeof(category) = 'text' AND length(trim(category)) > 0),
|
|
order_count INTEGER NOT NULL CHECK (typeof(order_count) = 'integer' AND order_count > 0),
|
|
revenue_kopecks INTEGER NOT NULL CHECK (typeof(revenue_kopecks) = 'integer' AND revenue_kopecks > 0),
|
|
PRIMARY KEY (order_date, category)
|
|
) WITHOUT ROWID;
|