day02

02.md

  1. SELECT
  2. exam_record.`exam_id`  AS tid,
  3. COUNT(DISTINCT exam_record.`uid`) AS uv,
  4. COUNT(*) AS pv
  5. FROM
  6. exam_record
  7. GROUP BY exam_record.`exam_id` 
  8. UNION ALL
  9. SELECT
  10. `practice_record`.`question_id` AS tid,
  11. COUNT(DISTINCT practice_record.`uid`) AS uv,
  12. COUNT(*)
  13. FROM
  14. practice_record
  15. GROUP BY practice_record.`question_id`

day03

03.md

  1. SELECT
  2. t1.product_id,
  3. ROUND(t1.num1/t2.num2,3) 
  4. FROM
  5. (SELECT
  6. tb_order_detail.`product_id`,
  7. COUNT( DISTINCT tb_order_overall.`uid`) num1
  8. FROM
  9. tb_order_overall,tb_order_detail
  10. WHERE tb_order_overall.`order_id`=tb_order_detail.`order_id`
  11. AND TIMESTAMP(NOW(),'%d')-TIMESTAMP(tb_order_overall.`event_time`,'%d')>=90
  12. GROUP BY tb_order_detail.`product_id` ,tb_order_overall.`uid` 
  13. HAVING COUNT(*)>1 ) AS t1,
  14. (
  15. SELECT
  16. tb_order_detail.`product_id`,
  17. COUNT( DISTINCT tb_order_overall.`uid`) num2
  18. FROM
  19. tb_order_overall,tb_order_detail
  20. WHERE tb_order_overall.`order_id`=tb_order_detail.`order_id`
  21. AND TIMESTAMP(NOW(),'%d')-TIMESTAMP(tb_order_overall.`event_time`,'%d')>=90
  22. GROUP BY tb_order_detail.`product_id` 
  23.  ) AS t2 ,tb_product_info
  24. WHERE
  25. t1.product_id=t2.product_id
  26. AND tb_product_info.`product_id`=t2.`product_id`
  27. AND tb_product_info.`tag`='零食'
  28. ORDER BY t1.product_id LIMIT 3

day04

day05

  1. drop table if exists examination_info,user_info,exam_record;
  2. CREATE TABLE examination_info (
  3.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  4.     exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
  5.     tag varchar(32) COMMENT '类别标签',
  6.     difficulty varchar(8) COMMENT '难度',
  7.     duration int NOT NULL COMMENT '时长',
  8.     release_time datetime COMMENT '发布时间'
  9. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  11. CREATE TABLE user_info (
  12.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  13.     uid int UNIQUE NOT NULL COMMENT '用户ID',
  14.     `nick_name` varchar(64) COMMENT '昵称',
  15.     achievement int COMMENT '成就值',
  16.     level int COMMENT '用户等级',
  17.     job varchar(32) COMMENT '职业方向',
  18.     register_time datetime COMMENT '注册时间'
  19. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  21. CREATE TABLE exam_record (
  22.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  23.     uid int NOT NULL COMMENT '用户ID',
  24.     exam_id int NOT NULL COMMENT '试卷ID',
  25.     start_time datetime NOT NULL COMMENT '开始时间',
  26.     submit_time datetime COMMENT '提交时间',
  27.     score tinyint COMMENT '得分'
  28. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  30. INSERT INTO user_info(uid,`nick_name`,achievement,`level`,job,register_time) VALUES
  31.   (1001, '牛客1', 19, 0, '算法', '2020-01-01 10:00:00'),
  32.   (1002, '牛客2号', 1200, 3, '算法', '2020-01-01 10:00:00'),
  33.   (1003, '牛客3号♂', 22, 0, '算法', '2020-01-01 10:00:00'),
  34.   (1004, '牛客4号', 25, 0, '算法', '2020-01-01 11:00:00'),
  35.   (1005, '牛客555号', 4000, 7, '算法', '2020-01-01 10:00:00'),
  36.   (1006, '牛客6号', 25, 0, '算法', '2020-01-02 11:00:00'),
  37.   (1007, '牛客7号', 25, 0, '算法', '2020-01-02 11:00:00'),
  38.   (1008, '牛客8号', 25, 0, '算法', '2020-01-02 11:00:00'),
  39.   (1009, '牛客9号', 25, 0, '算法', '2020-01-02 11:00:00'),
  40.   (1010, '牛客10号', 25, 0, '算法', '2020-01-02 11:00:00'),
  41.   (1011, '666666', 3000, 6, 'C++', '2020-01-02 10:00:00');
  43. INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  44.   (9001, '算法', 'hard', 60, '2020-01-01 10:00:00'),
  45.   (9002, '算法', 'hard', 80, '2020-01-01 10:00:00'),
  46.   (9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00');
  48. INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
  49. (1001, 9001, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 80),
  50. (1002, 9003, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 81),
  51. (1002, 9002, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83),
  52. (1003, 9002, '2020-01-01 19:01:01', '2020-01-01 19:30:01', 75),
  53. (1004, 9002, '2020-01-01 12:01:01', '2020-01-01 12:11:01', 60),
  54. (1005, 9002, '2020-01-01 12:01:01', '2020-01-01 12:41:01', 90),
  55. (1006, 9001, '2020-01-02 19:01:01', '2020-01-02 19:32:00', 20),
  56. (1007, 9002, '2020-01-02 19:01:01', '2020-01-02 19:40:01', 89),
  57. (1008, 9003, '2020-01-02 12:01:01', '2020-01-02 12:20:01', 99),
  58. (1008, 9001, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 98),
  59. (1009, 9002, '2020-01-02 12:01:01', '2020-01-02 12:31:01', 82),
  60. (1010, 9002, '2020-01-02 12:11:01', '2020-01-02 12:41:01', 76),
  61. (1011, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89);
  1. select user_info.uid,level,register_time,Max(score) from user_info join 
  2. (select uid ,submit_time, score ,exam_id from exam_record 
  3. where uid in  (select uid from user_info where job = '算法')) AS record on user_info.uid = record.uid 
  4. and DATE_FORMAT(user_info.register_time,'%Y%m%d')=DATE_FORMAT(record.submit_time,'%Y%m%d')
  5. group by record.uid order by score desc
  6. limit 6,3;