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

with base as (
    select
        t.*,
        a.module_name,
        a.total_calls
    from
        tenants t
    join lateral (
        select
            u.module_name,
            sum(u.call_count) as total_calls
        from
            usage_logs u 
        where
            u.tenant_id = t.tenant_id
        group by
            u.module_name
        order by
            total_calls desc 
        limit 2
    ) a on true
),
agg as (
    select
        	tenant_id,
            sum(call_count) as sum_
    from
        usage_logs
    group by
        	tenant_id
)
select
    b.tenant_name,
    plan_type,
    module_name,
    total_calls,
    round(total_calls * 100/sum_,2) as usage_pct
from
    base b
join
    agg a on b.tenant_id =a.tenant_id
order by
    b.tenant_id,total_calls desc,module_name