LeetCode SQL

Basic

Intermidate


Advanced


Specialized Problems


Set up Your Local Testing ENV

For better learning LeetCode SQL, you can set up your local MySQL ENV.

For example, open your MySQL Workbench and connect to your local SQL server. Run:

1
2
create database leetcode;
use leetcode;

For the LeetCode SQL problem, click on SQL Schema under the question name. Then copy the SQL schema from the right window.

Open VSCode or any text editor that supports Regular Exression. Paste the SQL schema to any new file.
Like this:

Press Command + F for OS X users or Control + F for Windows users.
Click on the button to turn on User Regular Expression

For the area Find, input \n
For the area Replace, input ;\n
This command is for replacing every line break with a ; then add a line break.
You must have ; for each SQL statement to make it valid.
Then click on Replace All

Then you will get the valid SQL INSERT statements.

Copy and paste them to your MySQL workbench for local testing.
Don’t remember to drop your test table if you have a table with the same name. Otherwise you cannot run this SQL INSERT statements.
For example, LeetCode usually use table Order to store order data. You may have multiple questions with the same table name but different table schema. So you need to drop it first.


Tools

str_to_sql can convert LeetCode SQL testcase to SQL INSERT statemens for better debugging in your local SQL env.

Source Cdoe:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
def str_to_sql(tb_string: str, tb_name: str, data_types=None) -> str:
"""
Convert a string representation of a table to an SQL INSERT statement.

Args:
tb_string: Multiline string with the first line as headers and the rest as data rows.
tb_name: Name of the SQL table to insert data into.
data_types(data type, optional): A set of data types to map each column.

Returns: SQL INSERT statement as a string.
"""
lines = tb_string.strip().split("\n")
headers = lines[0].split("|")[
1:-1
] # Skip the first and last empty splits from the borders
headers = [header.strip() for header in headers]

if data_types and len(data_types) != len(headers):
raise ValueError(
f"Expected {len(headers)} data types, received {len(data_types)} data types!"
)

values = []
# apply str to each column
if not data_types:
for line in lines[2:]: # Skip headers and '----'
row = line.split("|")[
1:-1
] # Skip the first and last empty splits from the borders
row = [i.strip() for i in row]
values.append(f"({', '.join(map(repr, row))})")
# map desired data type to each column
else:
for line in lines[2:]: # Skip headers and '----'
row = line.split("|")[
1:-1
] # Skip the first and last empty splits from the borders
row = [i.strip() for i in row]
i = 0
while i < len(data_types):
row[i] = data_types[i](row[i])
i += 1
values.append(f"({', '.join(map(repr, row))})")

columns = ", ".join(headers)
# multiline output
# values_string = ',\n'.join(values)
# single line output
values_string = ", ".join(values)

# multiline output
# return f"INSERT INTO {tb_name} ({columns}) VALUES\n{values_string};"
# single line output
return f"INSERT INTO {tb_name} ({columns}) VALUES {values_string};"

How get your failed testcase and use this program?

  1. Under the Input area of your Testcase, select the table and copy it.
  2. Paste the table string to your notebook and assign the value to variable tb_string. Assign tb_name with the same table name as your Testcase. Then run funciton str_to_sql.
  3. Notice you can also pass date types in a tuple to the parameter data_types.

Example 1:

1
2
3
4
5
6
7
8
9
10
11
tb_string = """
| player_id | device_id | event_date | games_played |
| --------- | --------- | ---------- | ------------ |
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
"""

str_to_sql(tb_string, 'Customer')

Output:

1
'INSERT INTO Customer (customer_id, product_key) VALUES ('1', '5'), ('2', '6'), ('3', '5'), ('3', '6'), ('1', '6');'

Example 2:

Use print to skip the ''
Pass data types to the parameter data_types to assign data type for each column.
If you skip the para data_types, the default data type for each column is string.

1
print(str_to_sql(tb_string, 'Activity', (int, int, str, int)))
1
INSERT INTO Activity (player_id, device_id, event_date, games_played) VALUES (1, 2, '2016-03-01', 5), (1, 2, '2016-05-02', 6), (2, 3, '2017-06-25', 1), (3, 1, '2016-03-02', 0), (3, 4, '2018-07-03', 5);