基于Events表，如何返回加州各城市应用安装率并按降序排序？

要计算加利福尼亚州各城市的应用安装率（有活动行为用户中完成安装的用户占比），并按安装率降序排序，我们需要先分别统计每个城市的有活动行为的去重用户数和完成安装的去重用户数，再计算两者的比值。以下是基于Events表的查询语句：

WITH city_user_metrics AS (
    SELECT
        city,
        -- 统计该城市所有有活动行为的去重用户
        COUNT(DISTINCT user_id) AS total_active_users,
        -- 统计该城市完成应用安装的去重用户
        COUNT(DISTINCT CASE WHEN event_type = 'install' THEN user_id END) AS installed_users
    FROM Events
    WHERE state = 'California' -- 筛选加利福尼亚州的数据
    GROUP BY city
)
SELECT
    city,
    -- 计算安装率（转换为百分比，保留2位小数），处理无活动用户的情况
    ROUND(
        CASE WHEN total_active_users > 0 THEN installed_users * 100.0 / total_active_users ELSE 0 END,
        2
    ) AS installation_rate_percent
FROM city_user_metrics
ORDER BY installation_rate_percent DESC;

关键说明： #

• 假设Events表包含字段：city（城市）、user_id（用户ID）、event_type（事件类型，其中install代表安装行为）、state（州，值为California）。如果你的字段名或枚举值不同（比如州用CA、安装事件用app_install），请对应调整语句中的条件。
• 使用COUNT(DISTINCT ...)确保每个用户只被统计一次，避免重复计数。
• 通过CASE WHEN处理除数为0的情况（当某个城市没有活动用户时，安装率设为0），避免查询报错。
• 最终结果按installation_rate_percent降序排列，优先展示安装率高的城市。

内容的提问来源于stack exchange，提问作者Ofir Talbi