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

with tm as(
    select tenant_id,module_name,sum(call_count) total_calls
    from usage_logs
    group by tenant_id,module_name
),
tt as(
    select tenant_id,module_name,total_calls,sum(total_calls)over(partition by tenant_id) tc,row_number()over(partition by tenant_id order by total_calls desc) p
    from tm
)
select tenant_name,plan_type,module_name,total_calls,round(total_calls/tc*100,2) as usage_pct
from tt 
left join tenants t on tt.tenant_id=t.tenant_id
where p=1 or p=2
order by tt.tenant_id,total_calls desc,module_name