Presto SQL query to calcute percent views

Question

I have to calculate % contribution for each category.

SELECT portfolio, (total_portfolio_views*1.00000/total_views)*100 view_contribution_perc
FROM(
select portfolio,sum(portfolio_views) AS total_portfolio_views, (select sum(portfolio_views) from gold.user_daily_osv) as total_views
from gold.user_daily_osv
group by 1
)
ORDER BY 2 DESC

Although this works fine, but it takes a lot of time to execute since these are huge tables, any other workaround for the same would be highly helpful.

Answer 1

You can try using sum window function to compute total views on the aggregate:

SELECT portfolio,
    (total_portfolio_views * 1.0 / sum(total_portfolio_views) over ()) * 100 view_contribution_perc
FROM(
        select portfolio,
            sum(portfolio_views) AS total_portfolio_views
        from gold.user_daily_osv
        group by 1
    )
ORDER BY 2 DESC