-- o.orderdate lowest date can only be 2023-03-20 since there is not DATA in the product_veiw table before that time period.

SELECT
pv_agg.part_code,
pv_agg.part_view_count,
pv_agg.type1_count as quickview_count,
pv_agg.type2_count as details_count,
COALESCE(od_agg.total_quantity, 0) as total_quantity,
ROUND(COALESCE(od_agg.total_quantity, 0)/pv_agg.part_view_count * 100, 2) as conversion_rate
FROM
(
SELECT
part_code,
COUNT(*) as part_view_count,
SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) as type1_count,
SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) as type2_count
FROM
product_view
GROUP BY
part_code
) pv_agg
LEFT JOIN
(
SELECT
productcode,
SUM(quantity) as total_quantity
FROM
orderdetails od
JOIN
orders o ON od.orderid = o.orderid
WHERE
o.orderstatus NOT IN ('Cancelled')
AND o.orderdate > '2023-03-20'
GROUP BY
productcode
) od_agg ON pv_agg.part_code = od_agg.productcode
ORDER BY
pv_agg.part_view_count DESC