Percentage of sales of customers who bought capris tights and a top

percentageOfSalesCaprisAndTop.sql

with hits as (
    SELECT
        count(*) totalTopAndCaprisPurchased
    FROM
        commons.customerActivity ca
    WHERE
        length(
            ARRAY_INTERSECT(
                ca.AddedToShoppingCart,
                ARRAY_CREATE('pkrcapris', 'pkrtop')
            )
        ) = 2
),
totals as (
    SELECT
        COUNT(*) as allPurchases
    FROM
        commons.customerActivity ca
)
SELECT
    Cast(h.totalTopAndCaprisPurchased as float) / cast(t.allPurchases as float) * 100
FROM
    hits h,
    totals t
;