Compete SQL with ChatGPT
I experimented with ChatGPT, a variation of the GPT-3 language model developed exclusively for conversational text generation. Of course, like the majority of us, I pondered if an AI could perform my duties. Can it perform the task better than I?
The basis of this game will be three LeetCode SQL problems. I will compose each solution before submitting the assignment to ChatGPT to determine which answer is the most effective. I will offer links to each assignment so you may attempt to beat ChatGPT as well.
ChatGPT is Not Available in Your Country? How to Bypass it
HChatGPT is accessible in the majority of industrialized nations, with the exception of Russia, China, Iran, Egypt, and, unexpectedly, Ukraine. The list of nations where ChatGPT is accessible may be seen on the official website of Open AI . If you dwell in any of these locations, ChatGPT should launch immediately. And if it does not, a network fault is likely to blame. Those who do not reside in a nation where ChatGPT is accessible might find the following information useful.
Step 1: Set up Use a VPN
You can always use a VPN to circumvent geographical limitations, but Chat GPT complicates matters.
Setting up Touch VPN as a Chrome extension.
Previously, users could access it via a server in a nation where the chatbot was accessible; today, a phone number is required. I can assist you in bypassing the phone validation in Step 2.
Step 2: Phone Number Validation
In this step, you need a credit card and about $0.68 to finish.
1. Go to Touch VPN and create an account.
2. After successfully login, move to the deposit page, chose Credit card and enter the amount 1 dollar enough. Clicking Deposit blue button.
3. You will move to another page for completing your purchase, just fill all needed information.
4. After purchasing, you will see $1 in the Balance section, with me it’s $1.5. Then move to the Quick Order for ordering a phone number, I recommend you choose the highest success rate then you can successfully approve for the first time. Click purchase then you can see your phone number on the right corner.
*Note: If you get the ChatGPT might be ‘at capacity’ , I have tip for you, just keep reloading the page until it’s asking to relog-in, now login again and then you can start to chat.
Challenge 1
This challenge is called Department Top Three Salaries.
HERE IS QUERY OF ME:
SELECT D.Name as Department, E.Name as Employee, E.Salary
FROM Department D, Employee E, Employee E2
WHERE D.ID = E.DepartmentId AND E.DepartmentId = E2.DepartmentId AND
E.Salary <= E2.Salary
group by D.ID,E.Name
having count(distinct E2.Salary) <= 3
My running time beats 65.74%
HERE IS QUERY OF ChatGPT:
But it’s getting error since ChatGPT use ‘rank’ as Alias but it’s MySQL keywork, let’s fix rank to rank_
WITH top_salaries AS (
SELECT departmentId, salary,
RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) as rank_
FROM Employee
)
SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM top_salaries ts
JOIN Employee e ON ts.id = e.id
JOIN Department d ON ts.departmentId = d.id
WHERE ts.rank_ <= 3
ORDER BY d.name, e.salary DESC
Sadly, the code just beats 5.4%.
Then, ROUND 1: I WIN ❤
This challenge is called Department Top Three Salaries.
HERE IS QUERY OF ME:
SELECT D.Name as Department, E.Name as Employee, E.Salary
FROM Department D, Employee E, Employee E2
WHERE D.ID = E.DepartmentId AND E.DepartmentId = E2.DepartmentId AND
E.Salary <= E2.Salary
group by D.ID,E.Name
having count(distinct E2.Salary) <= 3
My running time beats 65.74%
HERE IS QUERY OF ChatGPT:
But it’s getting error since ChatGPT use ‘rank’ as Alias but it’s MySQL keywork, let’s fix rank to rank_
WITH top_salaries AS (
SELECT departmentId, salary,
RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) as rank_
FROM Employee
)
SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM top_salaries ts
JOIN Employee e ON ts.id = e.id
JOIN Department d ON ts.departmentId = d.id
WHERE ts.rank_ <= 3
ORDER BY d.name, e.salary DESC
Sadly, the code just beats 5.4%.
Then, ROUND 1: I WIN ❤
Challenge 2
This challenge is called Trips and Users
HERE IS QUERY OF ME:
SELECT request_at as Day,
ROUND(COUNT(CASE WHEN Trips.status != 'completed' THEN 1 ELSE NULL END) /COUNT(*), 2) as "Cancellation Rate"
FROM Trips JOIN Users client
ON Trips.client_id = client.users_id
JOIN Users driver ON Trips.driver_id = driver.users_id
WHERE client.role = 'client'
AND driver.role = 'driver'
AND client.banned ='No'
AND driver.banned ='No'
AND request_at between '2013-10-01' and '2013-10-03'
GROUP BY request_at
ORDER BY request_at
My running time beats 6.58p%
HERE IS QUERY OF ChatGPT:
WITH
-- Get all unbanned user ids
unbanned_users AS (
SELECT users_id FROM Users WHERE banned = 'No'
),
-- Get all unbanned trip ids
unbanned_trips AS (
SELECT id FROM Trips
WHERE status IN ('cancelled_by_driver', 'cancelled_by_client')
AND client_id IN (SELECT users_id FROM unbanned_users)
AND driver_id IN (SELECT users_id FROM unbanned_users)
)
SELECT
DATE(request_at) as Day,
ROUND(COUNT(CASE WHEN id IN (SELECT id FROM unbanned_trips) THEN 1 ELSE NULL END) / COUNT(id), 2) as Cancellation_Rate
FROM Trips
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY DATE(request_at)
Sadly, It’s wrong answer.
Then, ROUND 2: I WIN AGAIN ❤
Challenge 3
This challenge is called Human Traffic of Stadium
HERE IS QUERY OF ME:
SELECT id, visit_date, people
FROM stadium
WHERE people>=100
AND
((id+1 in (SELECT id FROM stadium where people>=100) AND id+2 IN (SELECT id FROM stadium WHERE people>=100))
OR (id+1 in (SELECT id FROM stadium where people>=100) AND id-1 IN (SELECT id FROM stadium WHERE people>=100))
OR (id-1 in (SELECT id FROM stadium where people>=100) AND id-2 IN (SELECT id FROM stadium WHERE people>=100)));
Hmmm, I’m quite confident that ChatGPT will be defeated again cause my running time beats 96.75%.
HERE IS QUERY OF ChatGPT:
WITH CTE AS (
SELECT id, visit_date, people,
ROW_NUMBER() OVER (ORDER BY id) as rn
FROM Stadium
WHERE people >= 100
)
SELECT id, visit_date, people
FROM CTE
WHERE rn IN (
SELECT rn
FROM CTE
GROUP BY rn - id
HAVING COUNT(*) >= 3
)
ORDER BY visit_date;
ERROR SYNTAX AGAIN…
Then, ROUND 3: I WIN AGAIN ❤
Results
I believe it is accurate to state that I won this SQL contest versus ChatGPT. I was still surprised by its capabilities and astounded by how quickly it can answer the issue! ChatGPT surpasses me in terms of speed; it creates correct SQL syntax in a few seconds, when I need many minutes. However, it cannot surpass my QUERY PERFORMANCE.
However, due to the fact that all Leetcode Challenges are of the Difficult level, I believe ChatGPT can do better than I can since it is faster.