Presto SQL query to calcute percent views-CodePudding

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.

CodePudding user response：

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