SQL transaction, MySQL

SQL transaction

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version'
#product_dim: product_id, product_name, product_category
#store_dim: store_id, store_name, store_location
#customer_dim: customer_id, customer_name, shipping_address
#fact_sale_trans: trans_id, trans_date, store_id, cashier, customer_id, product_id, units_sold, price_per_unit

#1).generate a monthly trending report with the following metrics by store for the last 3 months.

SELECT f1.store_id,
COUNT(DISTINCT customer_id) AS total_customers,
SUM(units_sold*price_per_unit) AS total_revenue,
AVG(COUNT(DISTINCT customer_id)) AS avg_customers,
SUM(units_sold*price_per_unit) AS avg_revenue
FROM fact_sale_trans f1,
(SELECT * FROM fact_sale_trans WHERE trans_date >= DATEADD(CURDATE(), INTERVAL -3 MONTH)) f2
GROUP BY f1.store_id;

#2)identify the customers who are spending more than $25 in each visit
SELECT customer_id FROM(
SELECT customer_id, SUM(CASE WHEN spend > 25 THEN 1 ELSE null END) AS result
FROM(
SELECT trans_date, customer_id, SUM(price_per_unit*units_sold) AS spend
FROM fact_sale_trans
GROUP BY trans_date, customer_id
### GET: date | customer_id | spend
###      01-01|       1     |  20
###      01-02|       1     |  15
###      01-03|       1     |  25
###      01-01|       2     |  25
)a)a2
WHERE result IS NOT NULL


run \| edit \| history \| help	0