SQL Everyday

One SQL problem a day for a year

Description

SQL Everyday is a personal challenge to solve at least one SQL problem everyday for the next 365 days, starting from September 28, 2024.

The problems are selected from various websites devoted to online learning and technical job interview preparations. Below is a current list of these websites.

• Codewars
• DataCamp
• DataLemur
• LeetCode

Besides having the required set of SQL problems, they were chosen because they have an integrated database environment to work through to a solution.

All submitted solutions are then stored in this repo in their own markdown file. See the Contents section below for an index of those solutions.

To make the daily tasks of creating a new file and updating the index easier, there is a Jupyter notebook to automate that process.

(Coeus, whose name is derived from the Greek word 'koios', meaning 'query' or 'questioning', and who is associated with knowledge, determination and the inquisitive mind, here depicted second Titan from the left by Gustave Doré while attempting to solve a difficult SQL problem.)

Disclaimer

ALL CONTENTS IN THIS REPO ARE FOR EDUCATIONAL PURPOSES ONLY.

Getting Started

Dependencies

• See requirements.txt

Usage

• To automatically create a new solution file, open solution.ipynb in the templates directory and follow instructions.
• To check out submitted solutions and how they compare to those provided by the site, see Contents below.

Contents

Day	Title	Solution	Site	Difficulty	N2SELF
001	Histogram of Tweets	Solution	DataLemur	Easy
002	User's Third Transaction	Solution	DataLemur	Medium
003	Second Highest Salary	Solution	DataLemur	Medium
004	Sending vs. Opening Snaps	Solution	DataLemur	Medium	FILTER()
005	Highest-Grossing Items	Solution	DataLemur	Medium	RANK() vs DENSE_RANK
006	Top Three Salaries	Solution	DataLemur	Medium
007	Top 5 Artists	Solution	DataLemur	Medium
008	Signup Activation Rate	Solution	DataLemur	Medium
009	Supercloud Customer	Solution	DataLemur	Medium
010	Odd and Even Measurements	Solution	DataLemur	Medium	MOD()
011	Histogram of Users and Purchases	Solution	DataLemur	Medium
012	Compressed Mode	Solution	DataLemur	Medium	MODE()
013	Card Launch Success	Solution	DataLemur	Medium
014	International Call Percentage	Solution	DataLemur	Medium
015	Histogram of Tweets	Solution	DataLemur	Easy	EXTRACT() vs BETWEEN
016	Patient Support Analysis (Part 2)	Solution	DataLemur	Medium
017	Active User Retention	Solution	DataLemur	Hard
018	Data Science Skills	Solution	DataLemur	Easy
019	FAANG Stock Min-Max (Part 1)	Solution	DataLemur	Medium
020	Y-on-Y Growth Rate	Solution	DataLemur	Hard	LAG()
021	Page With No Likes	Solution	DataLemur	Easy
022	Swapped Food Delivery	Solution	DataLemur	Medium
023	Median Google Search Frequency	Solution	DataLemur	Hard	GENERATE_SERIES(), PERCENTILE_CONT()
024	Unfinished Parts	Solution	DataLemur	Easy
025	Fill Missing Client Data	Solution	DataLemur	Medium	FIRST_VALUE()
026	Advertiser Status	Solution	DataLemur	Hard
027	Laptop vs. Mobile Viewership	Solution	DataLemur	Easy
028	Spotify Streaming History	Solution	DataLemur	Medium
029	Consecutive Filing Years	Solution	DataLemur	Hard
030	Average Post Hiatus (Part 1)	Solution	DataLemur	Easy
031	Mean, Median, Mode	Solution	DataLemur	Medium	PERCENTILE_CONT()
032	Marketing Touch Streak	Solution	DataLemur	Hard
033	Teams Power Users	Solution	DataLemur	Easy
034	Pharmacy Analytics (Part 4)	Solution	DataLemur	Medium
035	3-Topping Pizzas	Solution	DataLemur	Hard
036	Well Paid Employees	Solution	DataLemur	Easy
037	Frequently Purchased Pairs	Solution	DataLemur	Medium	ARRAY_AGG(), ARRAY_LENGTH()
038	Department vs. Company Salary	Solution	DataLemur	Hard
039	Final Account Balance	Solution	DataLemur	Easy
040	Booking Referral Source	Solution	DataLemur	Medium
041	Compressed Median	Solution	DataLemur	Hard	GENERATE_SERIES(), PERCENTILE_CONT()
042	QuickBooks vs TurboTax	Solution	DataLemur	Easy
043	User Shopping Sprees	Solution	DataLemur	Medium
044	Average Vacant Days	Solution	DataLemur	Hard
045	App Click-through Rate (CTR)	Solution	DataLemur	Easy
046	2nd Ride Delay	Solution	DataLemur	Medium
047	Patient Support Analysis (Part 3)	Solution	DataLemur	Hard
048	Second Day Confirmation	Solution	DataLemur	Hard	INTERVAL, DATEADD(), DATE_ADD()
049	Google Maps Flagged UGC	Solution	DataLemur	Medium
050	Patient Support Analysis (Part 4)	Solution	DataLemur	Hard
051	IBM db2 Product Analytics	Solution	DataLemur	Easy	Histogram
052	LinkedIn Power Creators (Part 2)	Solution	DataLemur	Medium
053	Same Week Purchases	Solution	DataLemur	Hard
054	Cards Issued Difference	Solution	DataLemur	Easy
055	Unique Money Transfer Relationships	Solution	DataLemur	Medium	INTERSECT
056	Follow-Up Airpod Percentage	Solution	DataLemur	Hard
057	Compressed Mean	Solution	DataLemur	Easy
058	User Session Activity	Solution	DataLemur	Medium
059	Repeated Payments	Solution	DataLemur	Hard	EXTRACT w/ EPOCH
060	Pharmacy Analytics (Part 1)	Solution	DataLemur	Easy
061	First Transaction	Solution	DataLemur	Medium
062	FAANG Underperforming Stocks (Part 3)	Solution	DataLemur	Hard
063	Pharmacy Analytics (Part 2)	Solution	DataLemur	Easy
064	Email Table Transformation	Solution	DataLemur	Medium

Authors

• @ggeerraarrdd

Version History

Release Notes

• See https://github.com/ggeerraarrdd/sql-everyday/releases

Future Work

TBD

License

• MIT License

Acknowledgments

• TODO

Fronticepiece

Plate LXI: 'This proud one / Would of his strength against almighty Jove / Make trial' (Cary). Canto xxxi: Line 82: Page 165. Image taken from Dante Alighieri, Dante's Inferno. Translated by Henry Francis Cary. Illustrated by Gustave Doré. New York, London, and Paris: Cassell & Company Limited, 1866.