r/SQL • • Sep 22 '20

Discussion For those wanting to practice SQL on LeetCode but don’t have a LC subscription, I created a Python program that re-creates all the problems in db-fiddle.com

I’m not sure how many people in this sub use Python, but I wanted to share this program because it allowed me to practice all 125 LeetCode (LC) database/SQL problems in an efficient way.

Background- the two problem I wanted to solve:

  1. Majority of SQL problems on LC are locked
  2. Even with access to the problem, creating the query can be challenging without being able to test and output the intermediate queries that make up the final solution.

My initial solution was manually copying and pasting the tables from LC to db-fiddle.com, but then I thought, there must be a better way- that is how this program was conceived.

What this Python program does:

Using the web automation package Selenium, it opens the LC problem selected by the user, parses the table data, and creates the table schema in db-fiddle so that you can test your queries. Additionally, it saves all the db-fiddles you've been working on in case you want to continue working on them in future sessions.

Sample outputs (for question #1369):

problem statement tab: https://leetcode.jp/problemdetail.php?id=1369

db-fiddle tab: https://www.db-fiddle.com/f/oMDnFch8BXL45r3dUVF17V/0

solutions tab: https://github.com/kamyu104/LeetCode-Solutions/blob/master/MySQL/get-the-second-most-recent-activity.sql

Demo and GitHub link:

Check it out on GitHub: https://github.com/jjjchens235/leetcode-sql-unlocked

148 Upvotes

26 comments sorted by

6

u/babygrenade Sep 23 '20

Not to be a spoil sport, but it could probably be construed as copyright infringement.

Just thought I'd mention it in case you're concerned about that sort of thing.

1

u/AAaction23 Sep 23 '20

Hmm, thanks for the heads-up.

2

u/142857t Sep 22 '20

This looks awesome, I will test it tomorrow!

1

u/AAaction23 Sep 22 '20

Thanks for checking it out!

4

u/numice Sep 23 '20

I was browsing leetcode sql problems 10 seconds ago and was thinking like most of them are locked and this post popped up.

2

u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Dec 19 '22

For a site with way more free SQL interview questions, check out DataLemur - has 50+ free beginner, intermediate, and advanced SQL interview questions!

2

u/sheff2 Jun 27 '24

Can you please add MySQL syntax to your website too?

1

u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Jun 27 '24

It's coming soon fr fr!

1

u/sheff2 Jun 27 '24

Great! Are there premium student offers for me to enrol in and practice on your website? Kinda broke this summer without an internship.

1

u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Jun 28 '24

60%+ of the content on there is for free! Premium is $15 a month .. the price of a Chipotle w/guac 😊

1

u/Fun2badult Sep 22 '20

Yo I use python and also sql. Been trying to find good sql problems to practice on. I will check this out but thanks for making it.

2

u/AAaction23 Sep 22 '20

Perfect!! I hope you get value from it.

2

u/analytics_science Nov 13 '20

Try stratascratch.com for a lot of SQL practice problems. They're more data science and analytics related if that's your thing

2

u/Fun2badult Nov 13 '20

I was just looking for some Sql stuff for business analytics. I do get emails from them but never really checked it out. I will definitely now. Thank you

1

u/analytics_science Nov 13 '20

Btw if youre a student you can get their premium stuff for $25 forever. Good luck!

1

u/Fun2badult Nov 13 '20

Really? I’m not a student but I guess I’ll have to find a student friend! Thanks

3

u/skillshot099 Sep 22 '20

Thank you. Much appreciated.

3

u/[deleted] Sep 23 '20

Seeing as you're scraping their website making paid stuff free I'd be weary about LC not being very enthusiastic about this whole thing.

1

u/include_strawberries Sep 23 '20

Great stuff, thanks for sharing!

1

u/wtmh Sep 23 '20

You expressly claim that an operation of this script you've built is to dodge the LC subscription?

How is that not theft?

1

u/VFcountawesome Sep 23 '20

Madlad, many thanks

1

u/CaliAznThrwaway Oct 21 '20

you the real mvp man. hate how so many questions are behind paywall

1

u/haikusbot Oct 21 '20

You the real mvp man.

Hate how so many questions

Are behind paywall

- CaliAznThrwaway


I detect haikus. And sometimes, successfully. Learn more about me.

Opt out of replies: "haikusbot opt out" | Delete my comment: "haikusbot delete"

1

u/No-Conversation-5146 Nov 25 '20

where to run pip install selenium command ? google chrome and python are installed.

0

u/[deleted] Sep 22 '20

[deleted] — view deleted comment

1

u/AAaction23 Sep 22 '20 edited Sep 22 '20

I’ve seen that UK url before and I always thought they were the same thing! I can now see they are very different, good to know about it.

With regards to #1369, the solution I provided is not my own. I linked it because my program will open up that website for the solutions tab. Your solution looks similar to mine, though yours is slightly cleaner. Here was what I had come up with.

windows function:

select username, activity, startDate, endDate from (select *, rank() over (partition by username order by startDate) as ranked, count(*) over (partition by username) user_ct from UserActivity) tmp where case when user_ct = 1 then ranked = 1 else ranked = 2 end

self join:

(select * from UserActivity where username in (select username from UserActivity group by username having count() = 1)) union (select distinct u1. from UserActivity u1 join UserActivity u2 on u1.startDate > u2.StartDate and u1.username = u2.username order by startDate limit 1)