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

with temp0 as (
    select tenant_id,sum(call_count) as total_calls
    from usage_logs
    group by tenant_id
), temp1 as (
    select tenant_id,module_name,sum(call_count) as module_total_calls
    from usage_logs
    group by tenant_id,module_name
), temp2 as (
    select tenant_id,module_name,module_total_calls,
    row_number()over(partition by tenant_id order by module_total_calls desc,module_name asc) as rk
    from temp1
), temp3 as (
    select t3.tenant_name,t3.plan_type,module_name,module_total_calls as total_calls,round(module_total_calls*100/total_calls,2) as usage_pct
    from temp0 t0 inner join temp2 t2 on t0.tenant_id = t2.tenant_id
    inner join tenants t3 on t2.tenant_id = t3.tenant_id
    where rk <= 2
    order by t2.tenant_id asc,total_calls desc,module_name asc
)

select * from temp3;