牛客821079062号

分类

/ 注册

全部文章（共12篇）

题解 | 统计活跃间隔对用户分级结果

WITH t1 AS(SELECT DISTINCT uid, MAX(DATE(in_time)) OVER(PARTITION BY uid) AS act, MAX(DATE(in_time)) OVER() AS to_day, MIN(DATE(in_time)) OVE...

2025-06-29

0 10

题解 | 电话号码格式校验

SELECT id,name,phone_number FROM contacts WHERE (LENGTH(phone_number) = 10 OR phone_number LIKE '___-___-____') AND phone_number NOT LIKE '0%...

2025-06-27

0 18

题解 | 电商平台需要对商品的销售和评价情况进行综合分析

SELECT product_id,product_name,total_quantity,average_rating FROM products_underline JOIN ( SELECT product_id, ROUND(AVG(rating),2) AS average_ratin...

2025-06-18

0 25

题解 | 物流公司想要分析快递小哥的薪资构成和绩效情况

SELECT c.courier_id,courier_name, base_salary + total_fee - total_cost AS total_income FROM couriers_info c JOIN ( SELECT courier_id, SUM(deli...

2025-06-16

0 20

题解 | 查询出每个品牌在特定时间段内的退货率以及平均客户满意度评分

#查询出每个品牌在特定时间段内的退货率以及平均客户满意度评分 SELECT brand_id, brand_name, ROUND(SUM(IF(return_status = 1,1,0))/COUNT(return_status),2) AS return_rate_July_2024,...

2025-06-16

0 29

题解 | 分析每个员工在不同项目中的绩效情况

SELECT employee_id, employee_name, performance_score AS first_half_2024_score, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY performance_sc...

2025-06-16

0 24

题解 | 分析每个商品在不同时间段的销售情况

SELECT p.product_id, product_name, IFNULL(q2_2024_sales_total,0) AS q2_2024_sales_total, RANK() OVER (PARTITION BY category ORDER BY q2_2024_s...

2025-06-16

0 22

题解 | 电话号码格式校验

第一步：保证所有phone_number只有数字和 - SELECT id,name,phone_number, REPLACE(phone_number,'-','') AS nw FROM contacts WHERE REPLACE(phone_number,'-','') ...

2025-06-12

0 20

题解 | 查询连续登陆的用户

SELECT a.user_id FROM ( SELECT l.user_id, l.log_time FROM login_tb l LEFT JOIN login_tb a ON DATE(l.log_time) = DATE(a.log_time) -1 WHERE l.user_id = ...

2025-03-03

0 41

题解 | 淘宝店铺的实际销售额与客单价

SELECT sum(sales_num*goods_price) AS sales_total, sum(sales_num*goods_price)/count(DISTINCT user_id) AS per_trans FROM sales_tb JOIN goods_tb USI...

2025-02-18

0 70