deasdia

WITH a AS (
SELECT TransactionNo, COUNT(DISTINCT Items) AS numofitems
FROM `market-basket-analysis-project.marketbasketanalysis.sample`
GROUP BY 1 )

SELECT numofitems, COUNT(numofitems) Item_count, SUM(COUNT(numofitems )) OVER() total, COUNT(numofitems)/SUM(COUNT(numofitems )) OVER()*100 percentage
FROM a 
GROUP BY 1
ORDER BY 1

deasdia

-- get table ready for market basket analysis
WITH t1 AS (
SELECT a.items item1, b.items item2, CONCAT(a.items," -> ",b.items) AS combo, COUNT(*) numberoforders, 
COUNT(*)/(select COUNT(DISTINCT TransactionNo) FROM `market-basket-analysis-project.marketbasketanalysis.sample`) combo_support
FROM 
((SELECT DISTINCT TransactionNo, items
FROM `market-basket-analysis-project.marketbasketanalysis.sample`) a
JOIN
(SELECT DISTINCT TransactionNo, items
FROM `market-basket-analysis-project.marketbasketanalysis.sample`) b 

deasdia

SELECT COUNT(DISTINCT TransactionNo) total_unique_transactions,COUNT(DISTINCT Items) total_unique_items, FORMAT_DATE("%F",MIN(datetime)) start_datetime, FORMAT_DATE("%F",MAX(datetime)) end_datetime
FROM `market-basket-analysis-project.marketbasketanalysis.sample`

deasdia

SELECT Daypart, COUNT(TransactionNo) Order_ct
FROM `market-basket-analysis-project.marketbasketanalysis.sample`
GROUP BY 1
ORDER BY 2 DESC

deasdia

SELECT FORMAT_DATE("%A", datetime) Weekday_Name, COUNT(TransactionNo) ct,
CASE 
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 1 THEN 7
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 2 THEN 1
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 3 THEN 2
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 4 THEN 3
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 5 THEN 4
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 6 THEN 5
WHEN EXTRACT(DAYOFWEEK FROM datetime) = 7 THEN 6
END AS weekday_num

deasdia

SELECT EXTRACT(HOUR FROM datetime) time_of_day, COUNT(TransactionNo) ct,
FROM `market-basket-analysis-project.marketbasketanalysis.sample`
GROUP BY 1
ORDER BY 1

deasdia

SELECT Items, COUNT(Items) Item_ct
FROM `market-basket-analysis-project.marketbasketanalysis.sample`
GROUP BY 1
ORDER BY 2 DESC
LIMIT 30

deasdia

WITH a AS (
SELECT FORMAT_DATE('%Y-%m',tran_date) order_month, prod_cat, SUM(total_amt) Sales_amount, COUNT(QTY) sales_quantity
FROM `retail-case-study.retail_analysis.Maintable` 
GROUP BY 1,2),

b AS (
SELECT order_month, prod_cat, Sales_amount, LAG(Sales_amount) OVER (ORDER BY order_month) previous_month_sales, sales_quantity, LAG(sales_quantity) OVER (ORDER BY order_month) previous_sales_quantity
FROM a),

c AS (

deasdia

-- Include only buy, exclude return
WITH a AS (
select *,EXTRACT(YEAR FROM DOB) birth_year
FROM `retail-case-study.retail_analysis.Maintable`
WHERE total_amt > 0 ),

buy_survivorship AS (
SELECT generation, times, COUNT(DISTINCT cust_id) cohort,
COUNT(DISTINCT CASE WHEN purchase_frequency >= times THEN cust_id END) AS cohort_survived,
COUNT(DISTINCT CASE WHEN purchase_frequency >= times THEN cust_id END)/COUNT(DISTINCT cust_id)*100.0 AS survive_ratio

deasdia

WITH a AS (
select  cust_id, tran_date, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY tran_date) num
FROM `retail-case-study.retail_analysis.Maintable` 
ORDER BY 1,2 ),

b AS (
SELECT cust_id, tran_date, num,
CASE WHEN num = 1 THEN 'new' ELSE 'repeat' END status
FROM a
)