James Chapman
47d13ba922
All checks were successful
Docker Image CI / build (push) Successful in 3m45s
36 lines
850 B
SQL
36 lines
850 B
SQL
DROP VIEW item_purchases;
|
|
DROP VIEW transaction_totals;
|
|
|
|
ALTER TABLE transaction_items
|
|
ALTER COLUMN quantity TYPE numeric(5, 3);
|
|
|
|
ALTER TABLE transaction_items
|
|
ADD COLUMN IF NOT EXISTS unit text;
|
|
|
|
CREATE VIEW item_purchases AS
|
|
SELECT
|
|
item_id,
|
|
transaction_id,
|
|
created_at,
|
|
store_id,
|
|
price,
|
|
quantity,
|
|
unit,
|
|
CASE ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY created_at DESC)
|
|
WHEN 1 THEN true
|
|
ELSE false
|
|
END AS is_last_purchase
|
|
FROM transaction_items
|
|
JOIN transactions USING (transaction_id);
|
|
|
|
CREATE VIEW transaction_totals AS
|
|
SELECT transaction_id, sum(amount) AS total
|
|
FROM (
|
|
SELECT transaction_id, price * quantity AS amount
|
|
FROM transaction_items
|
|
UNION ALL
|
|
SELECT transaction_id, -amount
|
|
FROM transaction_promotions
|
|
) AS transaction_amounts
|
|
GROUP BY transaction_id;
|