--- highlight: a11y-dark ---

> 携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第12天，[点击查看活动详情](https://juejin.cn/post/7123120819437322247) > - 💡 作者：[韩信子](https://github.com/HanXinzi-AI)@[ShowMeAI](https://www.showmeai.tech/) > - 📘 [数据分析◉技能提升系列](https://www.showmeai.tech/tutorials/33)：[https://www.showmeai.tech/tutorials/33](https://www.showmeai.tech/tutorials/33) > - 📘 [AI 面试题库系列](https://www.showmeai.tech/tutorials/48)：[https://www.showmeai.tech/tutorials/48](https://www.showmeai.tech/tutorials/48) > - 📘 [本文地址](https://www.showmeai.tech/article-detail/297)：[https://www.showmeai.tech/article-detail/297](https://www.showmeai.tech/article-detail/297) > - 📢 声明：版权所有，转载请联系平台与作者并注明出处 > - 📢 收藏[ShowMeAI](https://www.showmeai.tech/)查看更多精彩内容下面是最新的 3 道 Google SQL 面试题和参考答案。这些题目面向的 Google 职位包括：**数据科学** **家**、**数据分析师**、**商业智能** **工程师**、**数据工程师**和**商业分析师**。

> [ShowMeAI](https://www.showmeai.tech/) 制作了快捷即查即用的 SQL 速查表手册，大家可以在下述位置获得： > - [**编程语言速查表 | SQL 速查表**](https://www.showmeai.tech/article-detail/99) # 💡 面试题 1：墨西哥和美国第三高峰

问题：给定下表，表中包含有关页面状态更改时间的信息。完成 SQL 查找当前使用 `latest_event` 的页面数。注意，表中 `page_flag` 列将用于识别页面是『OFF』还是『ON』。 ```sql Table: pages_info +-------+--------------------------------------+----------+ |page_id|event_time |page_flag | +-------+--------------------------------------+----------+ |1 |current_timestamp - interval '6 hours'|ON | |1 |current_timestamp - interval '3 hours'|OFF | |1 |current_timestamp - interval '1 hours'|ON | |2 |current_timestamp - interval '3 hours'|ON | |2 |current_timestamp - interval '1 hours'|OFF | |3 |current_timestamp |ON | +-------+--------------------------------------+----------+ ``` 参考答案： ```sql -- 首先，对于每个页面ID，让我们选择最新的记录（基于事件时间列）。 SELECT page_id, Max(event_time) AS latest_event FROM pages_info GROUP BY page_id -- 接着，我们将前面的查询与原表连接起来，并检查其中有多少人的标记页等于ON。 WITH latest_event AS (SELECT page_id, Max(event_time) AS latest_event FROM pages_info GROUP BY page_id) SELECT Sum(CASE WHEN page_flag = 'ON' THEN 1 ELSE 0 END) AS result FROM pages_info pi JOIN latest_event le ON pi.page_id = le.page_id AND pi.event_time = le.latest_event; ``` # 💡 面试题 3：回访用户

问题：在如下的数据库表中，包含有关用户访问网页的信息。完成 SQL 返回连续访问该页面最长的 3 个用户，按长短的倒序排列 3 个用户。 ```sql Table: visits +--------+----------------------------+ |user_id |date | +--------+----------------------------+ |1 |current_timestamp::DATE - 0 | |1 |current_timestamp::DATE - 1 | |1 |current_timestamp::DATE - 2 | |1 |current_timestamp::DATE - 3 | |1 |current_timestamp::DATE - 4 | |2 |current_timestamp::DATE - 1 | |4 |current_timestamp::DATE - 0 | |4 |current_timestamp::DATE - 1 | |4 |current_timestamp::DATE - 3 | |4 |current_timestamp::DATE - 4 | |4 |current_timestamp::DATE - 62| +--------+----------------------------+ ``` 参考答案： ```sql --首先，让我们添加一个新的列，其值是每个用户的下一次访问（与当前日期不同）。我们将使用lead函数来完成: SELECT DISTINCT user_id, date, Lead(date) OVER ( partition BY user_id ORDER BY date) AS next_date FROM (SELECT DISTINCT * FROM visits) AS t; --接着，让我们创建另一个列，其目的是让我们知道访问的停止。这包括检查下一个日期是否与当前日期+1是否不同。 WITH next_dates AS (SELECT DISTINCT user_id, date, Lead(date) OVER ( partition BY user_id ORDER BY date) AS next_date FROM (SELECT DISTINCT * FROM visits) AS t) --去重 SELECT user_id, date, next_date, CASE WHEN next_date IS NULL OR next_date = date + 1 THEN 1 ELSE NULL END AS streak FROM next_dates; --接着，我们将为每个用户创建一个分区，每个分区代表一个连续的访问。从概念上讲，我们要做的是，对于每个用户，取最近的记录（基于日期）并赋值为0，然后寻找下面的记录，如果访问没有停止就赋值为0，如果访问停止就赋值为1（如果连胜列为空），然后继续这样做，直到每个连续访问被一个不同的分区所代表。执行这一逻辑的代码如下。 WITH next_dates AS (SELECT DISTINCT user_id, date, Lead(date) OVER ( partition BY user_id ORDER BY date) AS next_date FROM (SELECT DISTINCT * FROM visits)), streaks AS (SELECT user_id, date, next_date, CASE WHEN next_date IS NULL OR next_date = date + 1 THEN 1 ELSE NULL END AS streak FROM next_dates) SELECT *, Sum(CASE WHEN streak IS NULL THEN 1 ELSE 0 END) OVER ( partition BY user_id ORDER BY date) AS partition FROM streaks; --一旦我们有了这个分区，问题就容易了，现在我们只需要计算每个用户和分区的记录数，并找到计数最多的用户。完整的查询如下 WITH next_dates AS ( SELECT DISTINCT user_id, date, Lead(date) OVER (partition BY user_id ORDER BY date) AS next_date FROM visits ), streaks AS ( SELECT user_id, date, next_date, CASE WHEN next_date IS NULL OR next_date = date + 1 THEN 1 ELSE NULL END AS streak FROM next_dates ), partitions AS ( SELECT *, Sum( CASE WHEN streak IS NULL THEN 1 ELSE 0 END ) OVER (partition BY user_id ORDER BY date) AS partition FROM streaks ), count_partitions AS ( SELECT user_id, partition, Count(1) AS streak_days FROM partitions GROUP BY user_id, partition ) SELECT user_id, Max(streak_days) AS longest_streak FROM count_partitions GROUP BY user_id ORDER BY 2 DESC limit 3; ``` # 参考资料 - 📘 **编程语言速查表 | SQL 速查表**：[https://www.showmeai.tech/article-detail/99](https://www.showmeai.tech/article-detail/99)