题解 | SaaS产品租户核心功能模块用量及占比分析

with lsb1 as (select tenant_name, plan_type, sum(call_count) as zongs
from tenants t
join usage_logs u 
on t.tenant_id = u.tenant_id
group by tenant_name, plan_type),
lsb2 as (
select tenant_name, module_name, sum(call_count) as total_calls
from tenants t
join usage_logs u 
on t.tenant_id = u.tenant_id
group by tenant_name, module_name)
select t.tenant_name,t.plan_type, t.module_name, t.total_calls,t.usage_pct
from 
(select l1.tenant_name,l1.plan_type, l2.module_name, l2.total_calls,
row_number() over(partition by tenant_name order by round((l2.total_calls/l1.zongs*100),2) desc) as rk,
round((l2.total_calls/l1.zongs*100),2) as usage_pct
from lsb1 l1
join lsb2 l2 
on l1.tenant_name = l2.tenant_name) t
where t.rk <= 2
order by t.usage_pct desc