📣 黑五价提前开抢|VIP一年通行证5折,蓝莓、Offer多多同步特惠
楼主: Myron2017
跳转到指定楼层
上一主题 下一主题
收起左侧

刷题记录帖子

🔗
 楼主| Myron2017 昨天 10:55 | 只看该作者
全局:
2118. Build the Equation
Hard
Topics
SQL Schema
Pandas Schema
Table: Terms

+-------------+------+
| Column Name | Type |
+-------------+------+
| power       | int  |
| factor      | int  |
+-------------+------+
power is the column with unique values for this table.
Each row of this table contains information about one term of the equation.
power is an integer in the range [0, 100].
factor is an integer in the range [-100, 100] and cannot be zero.


You have a very powerful program that can solve any equation of one variable in the world. The equation passed to the program must be formatted as follows:

The left-hand side (LHS) should contain all the terms.
The right-hand side (RHS) should be zero.
Each term of the LHS should follow the format "<sign><fact>X^<pow>" where:
<sign> is either "+" or "-".
<fact> is the absolute value of the factor.
<pow> is the value of the power.
If the power is 1, do not add "^<pow>".
For example, if power = 1 and factor = 3, the term will be "+3X".
If the power is 0, add neither "X" nor "^<pow>".
For example, if power = 0 and factor = -3, the term will be "-3".
The powers in the LHS should be sorted in descending order.
Write a solution to build the equation.

The result format is in the following example.



Example 1:

Input:
Terms table:
+-------+--------+
| power | factor |
+-------+--------+
| 2     | 1      |
| 1     | -4     |
| 0     | 2      |
+-------+--------+
Output:
+--------------+
| equation     |
+--------------+
| +1X^2-4X+2=0 |
+--------------+
Example 2:

Input:
Terms table:
+-------+--------+
| power | factor |
+-------+--------+
| 4     | -4     |
| 2     | 1      |
| 1     | -1     |
+-------+--------+
Output:
+-----------------+
| equation        |
+-----------------+
| -4X^4+1X^2-1X=0 |
+-----------------+


Follow up: What will be changed in your solution if the power is not a primary key but each power should be unique in the answer?
  1. # Write your MySQL query statement below
  2. SELECT CONCAT(
  3.     GROUP_CONCAT(
  4.         CONCAT(
  5.             IF(factor > 0, '+', '-'),
  6.             ABS(factor),
  7.             CASE
  8.                 WHEN power = 0 THEN ''
  9.                 WHEN power = 1 THEN 'X'
  10.                 ELSE CONCAT('X^', power)
  11.             END
  12.         )
  13.         ORDER BY power DESC
  14.         SEPARATOR ''
  15.     ),
  16.     '=0'
  17. ) AS equation
  18. FROM Terms;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:56 | 只看该作者
全局:
2199. Finding the Topic of Each Post
Hard
Topics
SQL Schema
Pandas Schema
Table: Keywords

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| topic_id    | int     |
| word        | varchar |
+-------------+---------+
(topic_id, word) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the id of a topic and a word that is used to express this topic.
There may be more than one word to express the same topic and one word may be used to express multiple topics.


Table: Posts

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| post_id     | int     |
| content     | varchar |
+-------------+---------+
post_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID of a post and its content.
Content will consist only of English letters and spaces.


Leetcode has collected some posts from its social media website and is interested in finding the topics of each post. Each topic can be expressed by one or more keywords. If a keyword of a certain topic exists in the content of a post (case insensitive) then the post has this topic.

Write a solution to find the topics of each post according to the following rules:

If the post does not have keywords from any topic, its topic should be "Ambiguous!".
If the post has at least one keyword of any topic, its topic should be a string of the IDs of its topics sorted in ascending order and separated by commas ','. The string should not contain duplicate IDs.
Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Keywords table:
+----------+----------+
| topic_id | word     |
+----------+----------+
| 1        | handball |
| 1        | football |
| 3        | WAR      |
| 2        | Vaccine  |
+----------+----------+
Posts table:
+---------+------------------------------------------------------------------------+
| post_id | content                                                                |
+---------+------------------------------------------------------------------------+
| 1       | We call it soccer They call it football hahaha                         |
| 2       | Americans prefer basketball while Europeans love handball and football |
| 3       | stop the war and play handball                                         |
| 4       | warning I planted some flowers this morning and then got vaccinated    |
+---------+------------------------------------------------------------------------+
Output:
+---------+------------+
| post_id | topic      |
+---------+------------+
| 1       | 1          |
| 2       | 1          |
| 3       | 1,3        |
| 4       | Ambiguous! |
+---------+------------+
Explanation:
1: "We call it soccer They call it football hahaha"
"football" expresses topic 1. There is no other word that expresses any other topic.

2: "Americans prefer basketball while Europeans love handball and football"
"handball" expresses topic 1. "football" expresses topic 1.
There is no other word that expresses any other topic.

3: "stop the war and play handball"
"war" expresses topic 3. "handball" expresses topic 1.
There is no other word that expresses any other topic.

4: "warning I planted some flowers this morning and then got vaccinated"
There is no word in this sentence that expresses any topic. Note that "warning" is different from "war" although they have a common prefix.
This post is ambiguous.

Note that it is okay to have one word that expresses more than one topic.
  1. # Write your MySQL query statement below
  2. SELECT
  3.     p.post_id,
  4.     IFNULL(
  5.         GROUP_CONCAT(DISTINCT k.topic_id ORDER BY k.topic_id SEPARATOR ','),
  6.         'Ambiguous!'
  7.     ) AS topic
  8. FROM Posts p
  9. LEFT JOIN Keywords k
  10.   ON CONCAT(' ', LOWER(p.content), ' ') LIKE CONCAT('% ', LOWER(k.word), ' %')
  11. GROUP BY p.post_id;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:57 | 只看该作者
全局:
2175. The Change in Global Rankings
Medium
Topics
SQL Schema
Pandas Schema
Table: TeamPoints

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| team_id     | int     |
| name        | varchar |
| points      | int     |
+-------------+---------+
team_id contains unique values.
Each row of this table contains the ID of a national team, the name of the country it represents, and the points it has in the global rankings. No two teams will represent the same country.


Table: PointsChange

+---------------+------+
| Column Name   | Type |
+---------------+------+
| team_id       | int  |
| points_change | int  |
+---------------+------+
team_id contains unique values.
Each row of this table contains the ID of a national team and the change in its points in the global rankings.
points_change can be:
- 0: indicates no change in points.
- positive: indicates an increase in points.
- negative: indicates a decrease in points.
Each team_id that appears in TeamPoints will also appear in this table.


The global ranking of a national team is its rank after sorting all the teams by their points in descending order. If two teams have the same points, we break the tie by sorting them by their name in lexicographical order.

The points of each national team should be updated based on its corresponding points_change value.

Write a solution to calculate the change in the global rankings after updating each team's points.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
TeamPoints table:
+---------+-------------+--------+
| team_id | name        | points |
+---------+-------------+--------+
| 3       | Algeria     | 1431   |
| 1       | Senegal     | 2132   |
| 2       | New Zealand | 1402   |
| 4       | Croatia     | 1817   |
+---------+-------------+--------+
PointsChange table:
+---------+---------------+
| team_id | points_change |
+---------+---------------+
| 3       | 399           |
| 2       | 0             |
| 4       | 13            |
| 1       | -22           |
+---------+---------------+
Output:
+---------+-------------+-----------+
| team_id | name        | rank_diff |
+---------+-------------+-----------+
| 1       | Senegal     | 0         |
| 4       | Croatia     | -1        |
| 3       | Algeria     | 1         |
| 2       | New Zealand | 0         |
+---------+-------------+-----------+
Explanation:
The global rankings were as follows:
+---------+-------------+--------+------+
| team_id | name        | points | rank |
+---------+-------------+--------+------+
| 1       | Senegal     | 2132   | 1    |
| 4       | Croatia     | 1817   | 2    |
| 3       | Algeria     | 1431   | 3    |
| 2       | New Zealand | 1402   | 4    |
+---------+-------------+--------+------+
After updating the points of each team, the rankings became the following:
+---------+-------------+--------+------+
| team_id | name        | points | rank |
+---------+-------------+--------+------+
| 1       | Senegal     | 2110   | 1    |
| 3       | Algeria     | 1830   | 2    |
| 4       | Croatia     | 1830   | 3    |
| 2       | New Zealand | 1402   | 4    |
+---------+-------------+--------+------+
Since after updating the points Algeria and Croatia have the same points, they are ranked according to their lexicographic order.
Senegal lost 22 points but their rank did not change.
Croatia gained 13 points but their rank decreased by one.
Algeria gained 399 points and their rank increased by one.
New Zealand did not gain or lose points and their rank did not change.
  1. # Write your MySQL query statement below
  2. SELECT
  3.     team_id,
  4.     name,
  5.     CAST(old_rank AS SIGNED) - CAST(new_rank AS SIGNED) AS rank_diff
  6. FROM (
  7.     SELECT
  8.         t.team_id,
  9.         t.name,
  10.         RANK() OVER (ORDER BY t.points DESC, t.name)                     AS old_rank,
  11.         RANK() OVER (ORDER BY t.points + c.points_change DESC, t.name)   AS new_rank
  12.     FROM TeamPoints t
  13.     JOIN PointsChange c
  14.       ON c.team_id = t.team_id
  15. ) r;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:58 | 只看该作者
全局:
2173. Longest Winning Streak
Hard
Topics
SQL Schema
Pandas Schema
Table: Matches

+-------------+------+
| Column Name | Type |
+-------------+------+
| player_id   | int  |
| match_day   | date |
| result      | enum |
+-------------+------+
(player_id, match_day) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the ID of a player, the day of the match they played, and the result of that match.
The result column is an ENUM (category) type of ('Win', 'Draw', 'Lose').


The winning streak of a player is the number of consecutive wins uninterrupted by draws or losses.

Write a solution to count the longest winning streak for each player.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Matches table:
+-----------+------------+--------+
| player_id | match_day  | result |
+-----------+------------+--------+
| 1         | 2022-01-17 | Win    |
| 1         | 2022-01-18 | Win    |
| 1         | 2022-01-25 | Win    |
| 1         | 2022-01-31 | Draw   |
| 1         | 2022-02-08 | Win    |
| 2         | 2022-02-06 | Lose   |
| 2         | 2022-02-08 | Lose   |
| 3         | 2022-03-30 | Win    |
+-----------+------------+--------+
Output:
+-----------+----------------+
| player_id | longest_streak |
+-----------+----------------+
| 1         | 3              |
| 2         | 0              |
| 3         | 1              |
+-----------+----------------+
Explanation:
Player 1:
From 2022-01-17 to 2022-01-25, player 1 won 3 consecutive matches.
On 2022-01-31, player 1 had a draw.
On 2022-02-08, player 1 won a match.
The longest winning streak was 3 matches.

Player 2:
From 2022-02-06 to 2022-02-08, player 2 lost 2 consecutive matches.
The longest winning streak was 0 matches.

Player 3:
On 2022-03-30, player 3 won a match.
The longest winning streak was 1 match.


Follow up: If we are interested in calculating the longest streak without losing (i.e., win or draw), how will your solution change?
  1. # Write your MySQL query statement below
  2. WITH T AS (
  3.     SELECT
  4.         player_id,
  5.         result,
  6.         SUM(result <> 'Win') OVER (
  7.             PARTITION BY player_id
  8.             ORDER BY match_day
  9.         ) AS grp
  10.     FROM Matches
  11. ),
  12. G AS (
  13.     SELECT
  14.         player_id,
  15.         grp,
  16.         SUM(result = 'Win') AS streak
  17.     FROM T
  18.     GROUP BY player_id, grp
  19. )
  20. SELECT
  21.     player_id,
  22.     MAX(streak) AS longest_streak
  23. FROM G
  24. GROUP BY player_id;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:58 | 只看该作者
全局:
2159. Order Two Columns Independently
Medium
Topics
SQL Schema
Pandas Schema
Table: Data

+-------------+------+
| Column Name | Type |
+-------------+------+
| first_col   | int  |
| second_col  | int  |
+-------------+------+
This table may contain duplicate rows.


Write a solution to independently:

order first_col in ascending order.
order second_col in descending order.
The result format is in the following example.



Example 1:

Input:
Data table:
+-----------+------------+
| first_col | second_col |
+-----------+------------+
| 4         | 2          |
| 2         | 3          |
| 3         | 1          |
| 1         | 4          |
+-----------+------------+
Output:
+-----------+------------+
| first_col | second_col |
+-----------+------------+
| 1         | 4          |
| 2         | 3          |
| 3         | 2          |
| 4         | 1          |
+-----------+------------+
  1. # Write your MySQL query statement below
  2. SELECT a.first_col, b.second_col
  3. FROM (
  4.     SELECT first_col,
  5.            ROW_NUMBER() OVER (ORDER BY first_col) AS rn
  6.     FROM Data
  7. ) a
  8. JOIN (
  9.     SELECT second_col,
  10.            ROW_NUMBER() OVER (ORDER BY second_col DESC) AS rn
  11.     FROM Data
  12. ) b
  13.   ON a.rn = b.rn
  14. ORDER BY a.rn;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 11:00 | 只看该作者
全局:
2153. The Number of Passengers in Each Bus II
Hard
Topics
SQL Schema
Pandas Schema
Table: Buses

+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
| capacity     | int  |
+--------------+------+
bus_id contains unique values.
Each row of this table contains information about the arrival time of a bus at the LeetCode station and its capacity (the number of empty seats it has).
No two buses will arrive at the same time and all bus capacities will be positive integers.


Table: Passengers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| passenger_id | int  |
| arrival_time | int  |
+--------------+------+
passenger_id contains unique values.
Each row of this table contains information about the arrival time of a passenger at the LeetCode station.


Buses and passengers arrive at the LeetCode station. If a bus arrives at the station at a time tbus and a passenger arrived at a time tpassenger where tpassenger <= tbus and the passenger did not catch any bus, the passenger will use that bus. In addition, each bus has a capacity. If at the moment the bus arrives at the station there are more passengers waiting than its capacity capacity, only capacity passengers will use the bus.

Write a solution to report the number of users that used each bus.

Return the result table ordered by bus_id in ascending order.

The result format is in the following example.



Example 1:

Input:
Buses table:
+--------+--------------+----------+
| bus_id | arrival_time | capacity |
+--------+--------------+----------+
| 1      | 2            | 1        |
| 2      | 4            | 10       |
| 3      | 7            | 2        |
+--------+--------------+----------+
Passengers table:
+--------------+--------------+
| passenger_id | arrival_time |
+--------------+--------------+
| 11           | 1            |
| 12           | 1            |
| 13           | 5            |
| 14           | 6            |
| 15           | 7            |
+--------------+--------------+
Output:
+--------+----------------+
| bus_id | passengers_cnt |
+--------+----------------+
| 1      | 1              |
| 2      | 1              |
| 3      | 2              |
+--------+----------------+
Explanation:
- Passenger 11 arrives at time 1.
- Passenger 12 arrives at time 1.
- Bus 1 arrives at time 2 and collects passenger 11 as it has one empty seat.

- Bus 2 arrives at time 4 and collects passenger 12 as it has ten empty seats.

- Passenger 12 arrives at time 5.
- Passenger 13 arrives at time 6.
- Passenger 14 arrives at time 7.
- Bus 3 arrives at time 7 and collects passengers 12 and 13 as it has two empty seats.
  1. # Write your MySQL query statement below
  2. WITH RECURSIVE B AS (
  3.     SELECT
  4.         b.bus_id,
  5.         b.capacity,
  6.         ROW_NUMBER() OVER (ORDER BY b.arrival_time) AS rn,
  7.         (SELECT COUNT(*) FROM Passengers p
  8.          WHERE p.arrival_time <= b.arrival_time)    AS arrived
  9.     FROM Buses b
  10. ),
  11. R AS (
  12.     -- 第一辆车:没有人被前面的车带走
  13.     SELECT
  14.         rn,
  15.         bus_id,
  16.         LEAST(capacity, arrived) AS cnt,
  17.         LEAST(capacity, arrived) AS taken
  18.     FROM B
  19.     WHERE rn = 1

  20.     UNION ALL

  21.     -- 后面的车:在前面已带走的人数基础上递推
  22.     SELECT
  23.         b.rn,
  24.         b.bus_id,
  25.         LEAST(b.capacity, b.arrived - r.taken)            AS cnt,
  26.         r.taken + LEAST(b.capacity, b.arrived - r.taken)  AS taken
  27.     FROM R r
  28.     JOIN B b
  29.       ON b.rn = r.rn + 1
  30. )
  31. SELECT bus_id, cnt AS passengers_cnt
  32. FROM R
  33. ORDER BY bus_id;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 11:01 | 只看该作者
全局:
2142. The Number of Passengers in Each Bus I
Medium
Topics
SQL Schema
Pandas Schema
Table: Buses

+--------------+------+
| Column Name  | Type |
+--------------+------+
| bus_id       | int  |
| arrival_time | int  |
+--------------+------+
bus_id is the column with unique values for this table.
Each row of this table contains information about the arrival time of a bus at the LeetCode station.
No two buses will arrive at the same time.


Table: Passengers

+--------------+------+
| Column Name  | Type |
+--------------+------+
| passenger_id | int  |
| arrival_time | int  |
+--------------+------+
passenger_id is the column with unique values for this table.
Each row of this table contains information about the arrival time of a passenger at the LeetCode station.


Buses and passengers arrive at the LeetCode station. If a bus arrives at the station at time tbus and a passenger arrived at time tpassenger where tpassenger <= tbus and the passenger did not catch any bus, the passenger will use that bus.

Write a solution to report the number of users that used each bus.

Return the result table ordered by bus_id in ascending order.

The result format is in the following example.



Example 1:

Input:
Buses table:
+--------+--------------+
| bus_id | arrival_time |
+--------+--------------+
| 1      | 2            |
| 2      | 4            |
| 3      | 7            |
+--------+--------------+
Passengers table:
+--------------+--------------+
| passenger_id | arrival_time |
+--------------+--------------+
| 11           | 1            |
| 12           | 5            |
| 13           | 6            |
| 14           | 7            |
+--------------+--------------+
Output:
+--------+----------------+
| bus_id | passengers_cnt |
+--------+----------------+
| 1      | 1              |
| 2      | 0              |
| 3      | 3              |
+--------+----------------+
Explanation:
- Passenger 11 arrives at time 1.
- Bus 1 arrives at time 2 and collects passenger 11.

- Bus 2 arrives at time 4 and does not collect any passengers.

- Passenger 12 arrives at time 5.
- Passenger 13 arrives at time 6.
- Passenger 14 arrives at time 7.
- Bus 3 arrives at time 7 and collects passengers 12, 13, and 14.
  1. # Write your MySQL query statement below
  2. WITH B AS (
  3.     SELECT
  4.         bus_id,
  5.         arrival_time,
  6.         LAG(arrival_time) OVER (ORDER BY arrival_time) AS prev_time
  7.     FROM Buses
  8. )
  9. SELECT
  10.     b.bus_id,
  11.     COUNT(p.passenger_id) AS passengers_cnt
  12. FROM B b
  13. LEFT JOIN Passengers p
  14.   ON p.arrival_time <= b.arrival_time
  15. AND (b.prev_time IS NULL OR p.arrival_time > b.prev_time)
  16. GROUP BY b.bus_id
  17. ORDER BY b.bus_id;
复制代码
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号
隐私提醒:
  • ☑ 禁止发布广告,拉群,贴个人联系方式:找人请去🔗同学同事飞友,拉群请去🔗拉群结伴,广告请去🔗跳蚤市场,和 🔗租房广告|找室友
  • ☑ 论坛内容在发帖 30 分钟内可以编辑,过后则不能删帖。为防止被骚扰甚至人肉,不要公开留微信等联系方式,如有需求请以论坛私信方式发送。
  • ☑ 干货版块可免费使用 🔗超级匿名:面经(美国面经、中国面经、数科面经、PM面经),抖包袱(美国、中国)和录取汇报、定位选校版
  • ☑ 查阅全站 🔗各种匿名方法

本版积分规则

>
快速回复 返回顶部 返回列表