尧图网页设计 
教程【免费下载链接】ru-test-assignmentsТестовые задания для самостоятельного выполнения от разных it компаний项目地址https://gitcode.com/gh_mirrors/ru/ru-test-assignments点击查看免费下载本篇技术指南围绕仓库中 analytics/happy-games-studio-analitik-dannykh/README.md 记载的“Happy Games Studio 数据分析师”SQL 测试题展开完整还原三张订单域数据表结构、百万级测试数据生成方案并逐步给出四道聚合查询的解析与标准 SQL 答案。读完本文你将掌握订单明细粒度建模、窗口函数与月同比对比、HAVING 过滤分组等实战技巧可直接复用于真实数据分析面试与订单分析场景。一、任务背景与数据模型本测试题来自 Happy Games Studio游戏工作室的数据分析师岗位招聘要求应聘者独立完成一套包含建表、造数、查询的完整 SQL 实操任务。考核重点并非单条语句的编写而是对数据库建模、大数据量下的查询性能、时间维度聚合分析的综合理解。题目给出的数据模型是典型的电商/游戏商城订单三表结构从源码角度看仓库 sql 目录 中收录了大量同类 SQL 测试如 Sberbank、Alfabank、Samokat 的题库本任务是其中覆盖面最完整的一份——既要求建模、造数又要求四道不同难度的分析查询。三张表结构如下表名字段说明usersid,name,email,created_at用户主表id为唯一标识ordersid,user_id,total_price,created_at订单主表user_id外键关联用户total_price为订单总额order_itemsid,order_id,product_name,price,quantity订单明细表order_id外键关联订单记录商品单价与数量任务要求**每张表至少 100 万行1 百万条**测试数据这在数据建模上是一个明确的性能压力点即便是不带任何索引的裸表对百万行做JOIN与GROUP BY聚合也需要谨慎设计查询路径否则全表扫描将拖慢整个练习流程。二、数据库选型与 Schema 设计题目要求“在答复中说明所使用的数据库名称与版本并附上数据库结构 dump”。在选型上建议选择免费、跨平台、文档丰富的 PostgreSQL例如 16.x 版本理由如下对窗口函数、CTE、date_trunc等日期聚合函数的支持完善恰好覆盖后四道查询的全部需求生成百万级序列化测试数据非常方便generate_series在真实订单分析场景中如仓库 samokat-analitik-dannykh 下的orders.csv、warehouses.csv等数据集PostgreSQL 同样是最常见的分析型 SQL 环境。完整的建表 SchemaPostgreSQL 方言如下-- 用户表 CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单表 CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total_price NUMERIC(12,2) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单明细表 CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id), product_name VARCHAR(255) NOT NULL, price NUMERIC(12,2) NOT NULL, quantity INT NOT NULL CHECK (quantity 0) ); -- 为高频过滤与聚合字段建立索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at); CREATE INDEX idx_order_items_order_id ON order_items(order_id);设计要点说明id使用BIGSERIAL自增主键避免百万级插入时手写 ID 冲突total_price、price使用NUMERIC(12,2)精确十进制避免浮点误差污染金额统计created_at统一为TIMESTAMPTZ保证“最近一个月”“当前年份”“去年同期”等时区敏感查询结果一致三个外键/过滤字段的索引是后文四道查询在百万行数据上保持可用性的关键特别是orders(created_at)与orders(user_id)的组合直接服务于“近一年/近一月”时间窗口过滤。参照仓库中同类测试题的 dump 风格例如 Sberbank 员工表测试 使用CREATE TABLEINSERT INTO ... VALUES的方式给出完整可运行脚本本任务同样要求交付建表 造数的完整 dump。三、百万级测试数据生成方案在 PostgreSQL 中最优雅的造数方式是利用generate_series与随机函数组合。以下脚本可在几十秒内为三张表各生成 100 万行数据-- 1) 生成 100 万用户 INSERT INTO users (name, email, created_at) SELECT User_ || gs, user_ || gs || example.com, timestamp 2020-01-01 random() * interval 5 years FROM generate_series(1, 1000000) AS gs; -- 2) 生成 100 万订单随机挂到用户上时间分布近两年 INSERT INTO orders (user_id, total_price, created_at) SELECT floor(random() * 1000000) 1, -- 随机 user_id round((random() * 5000 10)::numeric, 2), -- 订单金额 10 ~ 5010 now() - random() * interval 730 days -- 近两年内随机时间 FROM generate_series(1, 1000000) AS gs; -- 3) 生成 100 万订单明细每个订单 1~5 个商品行 INSERT INTO order_items (order_id, product_name, price, quantity) SELECT floor(random() * 1000000) 1, -- 随机 order_id Product_ || (floor(random() * 100) 1), -- 100 种商品之一 round((random() * 500 1)::numeric, 2), -- 单价 1 ~ 501 floor(random() * 5) 1 -- 数量 1~5 FROM generate_series(1, 1000000) AS gs;造数要点random()返回[0,1)区间的浮点数配合floor与1可得到指定范围的整数timestamp 2020-01-01 random() * interval 5 years让用户注册时间均匀分布在五年内保证“当前年份”与“上一年”都有足够数据订单时间使用now() - random() * interval 730 days分布在最近两年确保“最近一个月”“最近一年”“今年 vs 去年同月”四道题的窗口都有数据可查建议造数前先关闭自动提交、批量提交或使用COPY导入可显著缩短百万行插入耗时若使用 MySQL可将BIGSERIAL换成BIGINT AUTO_INCREMENT、TIMESTAMPTZ换成DATETIME、interval换成DATE_SUB(NOW(), INTERVAL ...)思路完全一致。四、查询 1统计下单超过 10 次的用户题目找出每个下单超过 10 次的用户的订单总数。标准答案SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) 10 ORDER BY order_count DESC;解析按user_id分组后COUNT(*)即每个用户的订单数HAVING COUNT(*) 10在分组后过滤只保留下单超过 10 次的用户——这是WHERE无法替代的WHERE作用于分组前的行HAVING作用于分组后的聚合结果ORDER BY order_count DESC按订单数降序输出便于人工核验 top 用户。性能提示在 100 万行orders上本查询依赖idx_orders_user_id索引完成分组预排序如果仍需全表扫描PostgreSQL 会退化为 SortGroupAggregate在百万行规模下耗时仍在可接受范围但建议始终保留该索引。五、查询 2每个用户最近一个月的平均订单额题目计算每个用户最近一个月的平均订单金额。标准答案SELECT user_id, AVG(total_price) AS avg_order_amount FROM orders WHERE created_at date_trunc(month, now()) - interval 1 month AND created_at date_trunc(month, now()) GROUP BY user_id;解析date_trunc(month, now())返回当前自然月的 1 日 0 点时间窗口[当月月初 - 1 个月, 当月月初)精确覆盖“上一个完整自然月”避免误用now() - interval 30 days造成的滑动窗口偏差后者跨月且长度不等对每个用户用AVG(total_price)求月内订单均值无订单的用户自然不出现。若希望覆盖“最近 30 天”而非自然月可替换为WHERE created_at now() - interval 30 days语义不同按题面“最近一个月”一般取自然月更严谨。六、查询 3今年各月平均订单额与去年同期对比题目计算当前年份每个月的平均订单金额并与上一年同月份对比。标准答案PostgreSQLWITH monthly AS ( SELECT date_trunc(month, created_at) AS month, AVG(total_price) AS avg_amount FROM orders WHERE date_part(year, created_at) IN (date_part(year, now())::int, date_part(year, now())::int - 1) GROUP BY date_trunc(month, created_at) ) SELECT to_char(m.month, YYYY-MM) AS month, m.avg_amount AS current_year_avg, prev.avg_amount AS last_year_avg, round((m.avg_amount - prev.avg_amount) / NULLIF(prev.avg_amount, 0) * 100, 2) AS yoy_change_pct FROM monthly m JOIN monthly prev ON prev.month m.month - interval 1 year ORDER BY m.month;解析先用 CTE 将订单按date_trunc(month, created_at)聚合出“每月平均订单额”只需扫描一次orders后续自连接直接在内存结果上完成自连接条件prev.month m.month - interval 1 year精确定位上一年同月天然实现对“2026-03 对比 2025-03”式的月同比NULLIF(prev.avg_amount, 0)防止上年该月无数据时除零报错输出NULL表示无法计算to_char(m.month, YYYY-MM)输出可读的月份字符串若统计粒度为每个订单而非月份均值也可在 CTE 中按date_part(month, created_at)直接分组语义等价。若数据库为 MySQL可用DATE_FORMAT(created_at, %Y-%m)替换to_char用DATE_SUB或INTERVAL 1 YEAR实现同月偏移整体逻辑不变。七、查询 4近一年订单最多的 10 个用户及近一月均值题目找出最近一年内下单数量最多的 10 个用户并同时计算他们最近一个月的平均订单额。标准答案PostgreSQLWITH top_users AS ( SELECT user_id, COUNT(*) AS yearly_orders FROM orders WHERE created_at now() - interval 1 year GROUP BY user_id ORDER BY yearly_orders DESC LIMIT 10 ) SELECT tu.user_id, tu.yearly_orders, COALESCE(m.avg_monthly, 0) AS avg_monthly_amount FROM top_users tu LEFT JOIN ( SELECT user_id, AVG(total_price) AS avg_monthly FROM orders WHERE created_at date_trunc(month, now()) - interval 1 month AND created_at date_trunc(month, now()) GROUP BY user_id ) m ON m.user_id tu.user_id ORDER BY tu.yearly_orders DESC;解析第一步在“近一年”窗口内统计每个用户订单数并ORDER BY ... LIMIT 10选出 Top-10 活跃用户第二步复用第五节的“上一个月”聚合逻辑仅针对这 10 个用户二次计算月均订单额使用LEFT JOIN而非INNER JOIN若某 top 用户上个月没有订单avg_monthly为NULL用COALESCE(..., 0)兜底为 0保证 10 行结果不丢用户两段窗口条件写在两个子查询中语义独立清晰比一次性WHERE混合两个时间窗口更不易出错。从执行计划角度该查询是典型的两阶段聚合先过滤近一年 → 分组排序取前 10 → 再小范围过滤近一月orders(created_at)与orders(user_id)索引分别服务两个时间窗口的过滤百万行下性能良好。八、常见错误写法与改进示范题目允许“补充一个错误写法并解释原因”以下是一组典型反例及其问题分析对面试官而言这比单写正确答案更能体现候选人的 SQL 功底错误写法 1对应查询 1把聚合条件放进 WHERE-- 错误WHERE 无法引用聚合结果 SELECT user_id, COUNT(*) AS order_count FROM orders WHERE COUNT(*) 10 GROUP BY user_id;问题WHERE在GROUP BY之前求值此时COUNT(*)尚未计算语法上会直接报错语义上“按行过滤”和“按分组过滤”是两个不同阶段必须使用HAVING。错误写法 2对应查询 2滑动窗口当作自然月-- 错误30 天窗口跨月口径不严谨 SELECT user_id, AVG(total_price) AS avg_amount FROM orders WHERE created_at now() - interval 30 days GROUP BY user_id;问题now() - interval 30 days得到的是滚动 30 天区间会跨越自然月边界不同日期运行结果口径不同无法与“按自然月”的其他指标对齐正确做法是用date_trunc(month, now())取整月边界。错误写法 3对应查询 3错误使用日期别名参与计算-- 错误SELECT 别名在 WHERE/GROUP BY 中不可直接引用部分数据库行为不同 SELECT date_trunc(month, created_at) AS month, AVG(total_price) AS avg_amount FROM orders WHERE date_part(year, month) date_part(year, now()) GROUP BY month;问题WHERE与GROUP BY中的别名依赖执行顺序在多数数据库中不可用应显式写date_trunc(month, created_at)或者把聚合放进 CTE 中再引用列名。此外仅过滤“今年”而未引入“去年”数据无法完成同比。九、答案交付与自检清单按题目要求完整答复应包含以下五部分缺一不可数据库名称与版本例如PostgreSQL 16写清 major 版本即可数据库结构 dump三张表的完整CREATE TABLE脚本含类型、约束、索引测试数据填充脚本三张表各 100 万行的插入语句能一键复现四道查询的 SQL 与逐条解释每个查询写明思路、关键函数与结果口径可选错误写法及解释如上节所示至少给出 1 个反例并说明问题。建议在交付前用EXPLAIN ANALYZE检查四道查询在百万行数据上的执行计划确认是否走索引、是否存在全表扫描并验证四条语句的结果行数合理性EXPLAIN ANALYZE SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) 10;十、延伸思考从测试题到真实订单分析这道题覆盖的聚合能力在真实数据分析工作中几乎每天都会用到仓库中就有多个可直接对照练习的数据集例如 Samokat 订单数据含orders.csv、products.csv、warehouses.csv以及 Delimobil 测试数据、Wolt 分析师数据集 等。把本任务中的四类查询分组计数 HAVING、自然月聚合、月同比、Top-N 两阶段聚合迁移到这些数据集上即可形成一套完整的“订单域分析”练习闭环。从更广视角看仓库 sql 题库 中同类测试题还覆盖了自连接比较如 Sberbank 员工薪资大于上级、多表连接与时间过滤如 Alfabank 按年份与商品名筛选、日期区间判断如 Samokat 仓库营业状态统计等题型可见“时间维度 分组聚合”正是俄罗斯各 IT 公司数据分析师面试的高频考点本任务则是其中建模、造数、查询一次到位的综合型代表。赞分享教程【免费下载链接】ru-test-assignmentsТестовые задания для самостоятельного выполнения от разных it компаний项目地址https://gitcode.com/gh_mirrors/ru/ru-test-assignments点击查看免费下载相关推荐torchtitan 中的 Loss 收敛性验证分布式训练技术正确性的标准测试方法torchtitan 中的 Loss 收敛性验证分布式训练技术正确性的标准测试方法 本文基于 torchtitan 仓库的 converging.md htt教程Samokat 数据分析师测试任务全解析Power BI 报表建模与 SQL 查询实战Samokat 数据分析师测试任务全解析Power BI 报表建模与 SQL 查询实战 本篇技术指南围绕开源仓库中 Samokat 数据分析师аналити教程Sravni.ru 产品分析师候选人测试题实战解析SQL 聚合查询、概率统计推断与二分类建模Sravni.ru 产品分析师候选人测试题实战解析SQL 聚合查询、概率统计推断与二分类建模 本文基于 GitHub 加速计划 / ru / ru test教程上一篇Emoji Scavenger Hunt部署教程如何在个人服务器上搭建这款AI猜谜游戏下一篇终极指南10个Alpine Linux Docker镜像的核心优势为什么它比Ubuntu更好创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 Happy Games Studio 数据分析师测试题全解：亿级订单表 SQL 聚合查询实战指南
教程【免费下载链接】ru-test-assignmentsТестовые задания для самостоятельного выполнения от разных it компаний项目地址https://gitcode.com/gh_mirrors/ru/ru-test-assignments点击查看免费下载本篇技术指南围绕仓库中 analytics/happy-games-studio-analitik-dannykh/README.md 记载的“Happy Games Studio 数据分析师”SQL 测试题展开完整还原三张订单域数据表结构、百万级测试数据生成方案并逐步给出四道聚合查询的解析与标准 SQL 答案。读完本文你将掌握订单明细粒度建模、窗口函数与月同比对比、HAVING 过滤分组等实战技巧可直接复用于真实数据分析面试与订单分析场景。一、任务背景与数据模型本测试题来自 Happy Games Studio游戏工作室的数据分析师岗位招聘要求应聘者独立完成一套包含建表、造数、查询的完整 SQL 实操任务。考核重点并非单条语句的编写而是对数据库建模、大数据量下的查询性能、时间维度聚合分析的综合理解。题目给出的数据模型是典型的电商/游戏商城订单三表结构从源码角度看仓库 sql 目录 中收录了大量同类 SQL 测试如 Sberbank、Alfabank、Samokat 的题库本任务是其中覆盖面最完整的一份——既要求建模、造数又要求四道不同难度的分析查询。三张表结构如下表名字段说明usersid,name,email,created_at用户主表id为唯一标识ordersid,user_id,total_price,created_at订单主表user_id外键关联用户total_price为订单总额order_itemsid,order_id,product_name,price,quantity订单明细表order_id外键关联订单记录商品单价与数量任务要求**每张表至少 100 万行1 百万条**测试数据这在数据建模上是一个明确的性能压力点即便是不带任何索引的裸表对百万行做JOIN与GROUP BY聚合也需要谨慎设计查询路径否则全表扫描将拖慢整个练习流程。二、数据库选型与 Schema 设计题目要求“在答复中说明所使用的数据库名称与版本并附上数据库结构 dump”。在选型上建议选择免费、跨平台、文档丰富的 PostgreSQL例如 16.x 版本理由如下对窗口函数、CTE、date_trunc等日期聚合函数的支持完善恰好覆盖后四道查询的全部需求生成百万级序列化测试数据非常方便generate_series在真实订单分析场景中如仓库 samokat-analitik-dannykh 下的orders.csv、warehouses.csv等数据集PostgreSQL 同样是最常见的分析型 SQL 环境。完整的建表 SchemaPostgreSQL 方言如下-- 用户表 CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单表 CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total_price NUMERIC(12,2) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- 订单明细表 CREATE TABLE order_items ( id BIGSERIAL PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id), product_name VARCHAR(255) NOT NULL, price NUMERIC(12,2) NOT NULL, quantity INT NOT NULL CHECK (quantity 0) ); -- 为高频过滤与聚合字段建立索引 CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at); CREATE INDEX idx_order_items_order_id ON order_items(order_id);设计要点说明id使用BIGSERIAL自增主键避免百万级插入时手写 ID 冲突total_price、price使用NUMERIC(12,2)精确十进制避免浮点误差污染金额统计created_at统一为TIMESTAMPTZ保证“最近一个月”“当前年份”“去年同期”等时区敏感查询结果一致三个外键/过滤字段的索引是后文四道查询在百万行数据上保持可用性的关键特别是orders(created_at)与orders(user_id)的组合直接服务于“近一年/近一月”时间窗口过滤。参照仓库中同类测试题的 dump 风格例如 Sberbank 员工表测试 使用CREATE TABLEINSERT INTO ... VALUES的方式给出完整可运行脚本本任务同样要求交付建表 造数的完整 dump。三、百万级测试数据生成方案在 PostgreSQL 中最优雅的造数方式是利用generate_series与随机函数组合。以下脚本可在几十秒内为三张表各生成 100 万行数据-- 1) 生成 100 万用户 INSERT INTO users (name, email, created_at) SELECT User_ || gs, user_ || gs || example.com, timestamp 2020-01-01 random() * interval 5 years FROM generate_series(1, 1000000) AS gs; -- 2) 生成 100 万订单随机挂到用户上时间分布近两年 INSERT INTO orders (user_id, total_price, created_at) SELECT floor(random() * 1000000) 1, -- 随机 user_id round((random() * 5000 10)::numeric, 2), -- 订单金额 10 ~ 5010 now() - random() * interval 730 days -- 近两年内随机时间 FROM generate_series(1, 1000000) AS gs; -- 3) 生成 100 万订单明细每个订单 1~5 个商品行 INSERT INTO order_items (order_id, product_name, price, quantity) SELECT floor(random() * 1000000) 1, -- 随机 order_id Product_ || (floor(random() * 100) 1), -- 100 种商品之一 round((random() * 500 1)::numeric, 2), -- 单价 1 ~ 501 floor(random() * 5) 1 -- 数量 1~5 FROM generate_series(1, 1000000) AS gs;造数要点random()返回[0,1)区间的浮点数配合floor与1可得到指定范围的整数timestamp 2020-01-01 random() * interval 5 years让用户注册时间均匀分布在五年内保证“当前年份”与“上一年”都有足够数据订单时间使用now() - random() * interval 730 days分布在最近两年确保“最近一个月”“最近一年”“今年 vs 去年同月”四道题的窗口都有数据可查建议造数前先关闭自动提交、批量提交或使用COPY导入可显著缩短百万行插入耗时若使用 MySQL可将BIGSERIAL换成BIGINT AUTO_INCREMENT、TIMESTAMPTZ换成DATETIME、interval换成DATE_SUB(NOW(), INTERVAL ...)思路完全一致。四、查询 1统计下单超过 10 次的用户题目找出每个下单超过 10 次的用户的订单总数。标准答案SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) 10 ORDER BY order_count DESC;解析按user_id分组后COUNT(*)即每个用户的订单数HAVING COUNT(*) 10在分组后过滤只保留下单超过 10 次的用户——这是WHERE无法替代的WHERE作用于分组前的行HAVING作用于分组后的聚合结果ORDER BY order_count DESC按订单数降序输出便于人工核验 top 用户。性能提示在 100 万行orders上本查询依赖idx_orders_user_id索引完成分组预排序如果仍需全表扫描PostgreSQL 会退化为 SortGroupAggregate在百万行规模下耗时仍在可接受范围但建议始终保留该索引。五、查询 2每个用户最近一个月的平均订单额题目计算每个用户最近一个月的平均订单金额。标准答案SELECT user_id, AVG(total_price) AS avg_order_amount FROM orders WHERE created_at date_trunc(month, now()) - interval 1 month AND created_at date_trunc(month, now()) GROUP BY user_id;解析date_trunc(month, now())返回当前自然月的 1 日 0 点时间窗口[当月月初 - 1 个月, 当月月初)精确覆盖“上一个完整自然月”避免误用now() - interval 30 days造成的滑动窗口偏差后者跨月且长度不等对每个用户用AVG(total_price)求月内订单均值无订单的用户自然不出现。若希望覆盖“最近 30 天”而非自然月可替换为WHERE created_at now() - interval 30 days语义不同按题面“最近一个月”一般取自然月更严谨。六、查询 3今年各月平均订单额与去年同期对比题目计算当前年份每个月的平均订单金额并与上一年同月份对比。标准答案PostgreSQLWITH monthly AS ( SELECT date_trunc(month, created_at) AS month, AVG(total_price) AS avg_amount FROM orders WHERE date_part(year, created_at) IN (date_part(year, now())::int, date_part(year, now())::int - 1) GROUP BY date_trunc(month, created_at) ) SELECT to_char(m.month, YYYY-MM) AS month, m.avg_amount AS current_year_avg, prev.avg_amount AS last_year_avg, round((m.avg_amount - prev.avg_amount) / NULLIF(prev.avg_amount, 0) * 100, 2) AS yoy_change_pct FROM monthly m JOIN monthly prev ON prev.month m.month - interval 1 year ORDER BY m.month;解析先用 CTE 将订单按date_trunc(month, created_at)聚合出“每月平均订单额”只需扫描一次orders后续自连接直接在内存结果上完成自连接条件prev.month m.month - interval 1 year精确定位上一年同月天然实现对“2026-03 对比 2025-03”式的月同比NULLIF(prev.avg_amount, 0)防止上年该月无数据时除零报错输出NULL表示无法计算to_char(m.month, YYYY-MM)输出可读的月份字符串若统计粒度为每个订单而非月份均值也可在 CTE 中按date_part(month, created_at)直接分组语义等价。若数据库为 MySQL可用DATE_FORMAT(created_at, %Y-%m)替换to_char用DATE_SUB或INTERVAL 1 YEAR实现同月偏移整体逻辑不变。七、查询 4近一年订单最多的 10 个用户及近一月均值题目找出最近一年内下单数量最多的 10 个用户并同时计算他们最近一个月的平均订单额。标准答案PostgreSQLWITH top_users AS ( SELECT user_id, COUNT(*) AS yearly_orders FROM orders WHERE created_at now() - interval 1 year GROUP BY user_id ORDER BY yearly_orders DESC LIMIT 10 ) SELECT tu.user_id, tu.yearly_orders, COALESCE(m.avg_monthly, 0) AS avg_monthly_amount FROM top_users tu LEFT JOIN ( SELECT user_id, AVG(total_price) AS avg_monthly FROM orders WHERE created_at date_trunc(month, now()) - interval 1 month AND created_at date_trunc(month, now()) GROUP BY user_id ) m ON m.user_id tu.user_id ORDER BY tu.yearly_orders DESC;解析第一步在“近一年”窗口内统计每个用户订单数并ORDER BY ... LIMIT 10选出 Top-10 活跃用户第二步复用第五节的“上一个月”聚合逻辑仅针对这 10 个用户二次计算月均订单额使用LEFT JOIN而非INNER JOIN若某 top 用户上个月没有订单avg_monthly为NULL用COALESCE(..., 0)兜底为 0保证 10 行结果不丢用户两段窗口条件写在两个子查询中语义独立清晰比一次性WHERE混合两个时间窗口更不易出错。从执行计划角度该查询是典型的两阶段聚合先过滤近一年 → 分组排序取前 10 → 再小范围过滤近一月orders(created_at)与orders(user_id)索引分别服务两个时间窗口的过滤百万行下性能良好。八、常见错误写法与改进示范题目允许“补充一个错误写法并解释原因”以下是一组典型反例及其问题分析对面试官而言这比单写正确答案更能体现候选人的 SQL 功底错误写法 1对应查询 1把聚合条件放进 WHERE-- 错误WHERE 无法引用聚合结果 SELECT user_id, COUNT(*) AS order_count FROM orders WHERE COUNT(*) 10 GROUP BY user_id;问题WHERE在GROUP BY之前求值此时COUNT(*)尚未计算语法上会直接报错语义上“按行过滤”和“按分组过滤”是两个不同阶段必须使用HAVING。错误写法 2对应查询 2滑动窗口当作自然月-- 错误30 天窗口跨月口径不严谨 SELECT user_id, AVG(total_price) AS avg_amount FROM orders WHERE created_at now() - interval 30 days GROUP BY user_id;问题now() - interval 30 days得到的是滚动 30 天区间会跨越自然月边界不同日期运行结果口径不同无法与“按自然月”的其他指标对齐正确做法是用date_trunc(month, now())取整月边界。错误写法 3对应查询 3错误使用日期别名参与计算-- 错误SELECT 别名在 WHERE/GROUP BY 中不可直接引用部分数据库行为不同 SELECT date_trunc(month, created_at) AS month, AVG(total_price) AS avg_amount FROM orders WHERE date_part(year, month) date_part(year, now()) GROUP BY month;问题WHERE与GROUP BY中的别名依赖执行顺序在多数数据库中不可用应显式写date_trunc(month, created_at)或者把聚合放进 CTE 中再引用列名。此外仅过滤“今年”而未引入“去年”数据无法完成同比。九、答案交付与自检清单按题目要求完整答复应包含以下五部分缺一不可数据库名称与版本例如PostgreSQL 16写清 major 版本即可数据库结构 dump三张表的完整CREATE TABLE脚本含类型、约束、索引测试数据填充脚本三张表各 100 万行的插入语句能一键复现四道查询的 SQL 与逐条解释每个查询写明思路、关键函数与结果口径可选错误写法及解释如上节所示至少给出 1 个反例并说明问题。建议在交付前用EXPLAIN ANALYZE检查四道查询在百万行数据上的执行计划确认是否走索引、是否存在全表扫描并验证四条语句的结果行数合理性EXPLAIN ANALYZE SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) 10;十、延伸思考从测试题到真实订单分析这道题覆盖的聚合能力在真实数据分析工作中几乎每天都会用到仓库中就有多个可直接对照练习的数据集例如 Samokat 订单数据含orders.csv、products.csv、warehouses.csv以及 Delimobil 测试数据、Wolt 分析师数据集 等。把本任务中的四类查询分组计数 HAVING、自然月聚合、月同比、Top-N 两阶段聚合迁移到这些数据集上即可形成一套完整的“订单域分析”练习闭环。从更广视角看仓库 sql 题库 中同类测试题还覆盖了自连接比较如 Sberbank 员工薪资大于上级、多表连接与时间过滤如 Alfabank 按年份与商品名筛选、日期区间判断如 Samokat 仓库营业状态统计等题型可见“时间维度 分组聚合”正是俄罗斯各 IT 公司数据分析师面试的高频考点本任务则是其中建模、造数、查询一次到位的综合型代表。赞分享教程【免费下载链接】ru-test-assignmentsТестовые задания для самостоятельного выполнения от разных it компаний项目地址https://gitcode.com/gh_mirrors/ru/ru-test-assignments点击查看免费下载相关推荐torchtitan 中的 Loss 收敛性验证分布式训练技术正确性的标准测试方法torchtitan 中的 Loss 收敛性验证分布式训练技术正确性的标准测试方法 本文基于 torchtitan 仓库的 converging.md htt教程Samokat 数据分析师测试任务全解析Power BI 报表建模与 SQL 查询实战Samokat 数据分析师测试任务全解析Power BI 报表建模与 SQL 查询实战 本篇技术指南围绕开源仓库中 Samokat 数据分析师аналити教程Sravni.ru 产品分析师候选人测试题实战解析SQL 聚合查询、概率统计推断与二分类建模Sravni.ru 产品分析师候选人测试题实战解析SQL 聚合查询、概率统计推断与二分类建模 本文基于 GitHub 加速计划 / ru / ru test教程上一篇Emoji Scavenger Hunt部署教程如何在个人服务器上搭建这款AI猜谜游戏下一篇终极指南10个Alpine Linux Docker镜像的核心优势为什么它比Ubuntu更好创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考