时间戳

首页 > 解决方案 > 在 X 个不同日期玩过的用户 - SQL Standard + BigQuery

sql - 在 X 个不同日期玩过的用户 - SQL Standard + BigQuery

问题描述

我在带有 SQL 标准的 BigQuery 上有以下数据模型架构（我只有架构，没有表）。

我创建了这个查询来选择过去三个月在 Love 游戏中产生更多收入的前 10 名用户：

SELECT
  users.user_id,
  SUM(pay.amount) AS total_rev
FROM
  `my-database.User` AS users
INNER JOIN
  `my-database.IAP_events` AS pay
ON
  users.User_id = pay.User_id
INNER JOIN
  `my-database.Games` AS games
ON
  users.Game_id = games.Game_id
WHERE
  games.game_name = "Love"
GROUP BY
  users.user_id
ORDER BY
  total_rev ASC
LIMIT
  10

但随后，该练习表示仅考虑在过去 3 个月内玩过 10 天不同游戏的用户。我知道我会在日期中使用带有计数的子查询，但我对如何做到这一点有点迷失......

非常感谢！

标签： sqlgoogle-bigquery

解决方案

编辑：您需要计算不同的日期，而不是交易，因此在限定条款中您需要声明COUNT(DISTINCT date_) OVER ...而不是COUNT(transaction_id) OVER .... 已经修复了代码。

据我了解，您需要在前 3 个月的窗口中计算 IAP_Events 内的不同 transaction_id，检查该计数是否大于 10，然后将该约束中包含的所有用户的数量相加。

为此，您可以使用BigQuery 的分析函数，也就是窗口函数：

with window_counting as (
  select
    user_id,
    amount
  from 
    iap_events
  where
    date_ >= date_sub(current_date(), interval 3 month)
  qualify 
    count(distinct date_) over (partition by user_id) > 10
  
),
final as (
  select
    user_id,
    sum(amount)
  from
    window_counting
  group by
    1
  order by
    2 desc
  limit 10
)
select * from final

您只需要在第一个 CTE 中添加所需的连接，以便按 game_name 过滤:)

推荐阅读