Apriori Algorithm in SQL

apriori.sql

with base as
(
SELECT *
FROM Product_Details_002
where Product_ID is not null
),
inner_joined_cte as 
(
select a. Order_ID, a. Product_ID as puller_Art, b. Product_ID as pulled_art
from base as a
inner join 
base as b
on 
a.Order_ID = b. Order_ID 
),
total_txns_N as 
-- get the N i.e total transaction count
(
select count(distinct Order_ID ) as total_txns
from inner_joined_cte
),
total_puller_N as 
-- get the N  puller i.e total transaction count
(
select  puller_Art, count ( distinct Order_ID  ) as puller_txns
from inner_joined_cte
group by  1
-- order by puller_txns desc
),
total_pulled_N as 
(
select  Product_ID as pulled_Art, count (Order_ID  ) as pulled_txns
from 
(
select Order_ID , Product_ID 
from base
group by 1,2
)	
group by  1
),
total_intersection_N as -- A ∩ B count 
(
select pulled_art, puller_art, count (distinct  Order_ID) as intersec_txns
from inner_joined_cte
group by  pulled_art, puller_art
),
cross_join_table as 
(
select puller_art, pulled_art 
from inner_joined_cte
group by 1,2 
),
affinity_primary as (
select its. puller_art, its. pulled_art, 
safe_divide (intersec_txns, total_txns)  as Support, 
safe_divide (intersec_txns, puller_txns) as Confidence, 
safe_divide ((intersec_txns*total_txns),(puller_txns* pulled_txns)) as Lift
from cross_join_table as its
cross join total_txns_N 
left join 
total_intersection_N as tin
on 
its. puller_Art    = tin. puller_Art    and 
its. pulled_Art    = tin. pulled_Art 
left join 
total_puller_N as ant
on 
ant. puller_Art     = its. puller_Art 
left join 
total_pulled_N as con
on
con. pulled_Art     = its. pulled_Art 
)
select * from affinity_primary