SQL Everyday

One SQL problem a day for a year

Description

Note

ALL CONTENTS IN THIS REPO ARE FOR EDUCATIONAL PURPOSES ONLY.

Note

SQL Everyday has been templatized into eevveerryyddaayy.

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 websites devoted to online learning and technical job interview preparations. Currently, they include: Codewars, DataLemur and 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.

For every daily SQL problem, its description and both the submitted and site solutions are documented in individual markdown files, stored in solutions. For quick reference, those files are indexed below.

Because this project necessitated a framework to enable consistent daily practice, much of the tedious manual work has been streamlined. For instance, generating new solution files from a template and populating them with new data has been automated. Managing the ever-growing Index has also been automated. Additionally, data entry is handled through a Jupyter IPyWidgets form. These have freed up more time on what matters most—the actual learning.

(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.)

Index

Day	Title	Solution	Site	Difficulty	NB
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
065	User Concurrent Sessions	Solution	DataLemur	Hard
066	Pharmacy Analytics (Part 3)	Solution	DataLemur	Easy
067	Photoshop Revenue Analysis	Solution	DataLemur	Medium
068	Monthly Merchant Balance	Solution	DataLemur	Hard	PARTITION BY w/ DATE_TRUNC()
069	Patient Support Analysis (Part 1)	Solution	DataLemur	Easy
070	Consulting Bench Time	Solution	DataLemur	Medium
071	Bad Delivery Rate	Solution	DataLemur	Hard
072	Most Expensive Purchase	Solution	DataLemur	Easy
073	Sales Team Compensation	Solution	DataLemur	Medium
074	Page Recommendation	Solution	DataLemur	Hard
075	ApplePay Volume	Solution	DataLemur	Easy
076	Average Deal Size (Part 2)	Solution	DataLemur	Medium
077	Reactivated Users	Solution	DataLemur	Hard
078	Subject Matter Experts	Solution	DataLemur	Easy
079	Cumulative Purchases by Product Type	Solution	DataLemur	Medium
080	Senior Managers	Solution	DataLemur	Hard
081	LinkedIn Power Creators (Part 1)	Solution	DataLemur	Easy
082	Invalid Search Results	Solution	DataLemur	Medium
083	Event Friends Recommendation	Solution	DataLemur	Hard
084	Highest Number of Products	Solution	DataLemur	Easy
085	Repeat Purchases on Multiple Days	Solution	DataLemur	Medium	DATE_TRUNC vs ::DATE
086	Matching Rental Amenities	Solution	DataLemur	Hard	ARRAY_AGG()
087	Spare Server Capacity	Solution	DataLemur	Easy
088	Compensation Outliers	Solution	DataLemur	Medium
089	Top Rated Businesses	Solution	DataLemur	Easy
090	Ad Campaign ROAS	Solution	DataLemur	Easy
091	Product Line Revenue	Solution	DataLemur	Easy
092	Trade In Payouts	Solution	DataLemur	Easy
093	Webinar Popularity	Solution	DataLemur	Easy	DATE_TRUNC() vs BETWEEN
094	Who Made Quota?	Solution	DataLemur	Easy
095	Recyclable and Low Fat Products	Solution	LeetCode	Easy
096	Find Customer Referee	Solution	LeetCode	Easy
097	Big Countries	Solution	LeetCode	Easy
098	Article Views I	Solution	LeetCode	Easy
099	Invalid Tweets	Solution	LeetCode	Easy
100	Replace Employee ID With The Unique Identifier	Solution	LeetCode	Easy
101	Product Sales Analysis I	Solution	LeetCode	Easy
102	Customer Who Visited but Did Not Make Any Transactions	Solution	LeetCode	Easy
103	Rising Temperature	Solution	LeetCode	Easy
104	Average Time of Process per Machine	Solution	LeetCode	Easy
105	Employee Bonus	Solution	LeetCode	Easy
106	Students and Examinations	Solution	LeetCode	Easy
107	Managers with at Least 5 Direct Reports	Solution	LeetCode	Medium
108	Confirmation Rate	Solution	LeetCode	Medium
109	Not Boring Movies	Solution	LeetCode	Easy
110	Average Selling Price	Solution	LeetCode	Easy
111	Project Employees I	Solution	LeetCode	Easy
112	Percentage of Users Attended a Contest	Solution	LeetCode	Easy
113	Queries Quality and Percentage	Solution	LeetCode	Easy
114	Monthly Transactions I	Solution	LeetCode	Medium
115	Immediate Food Delivery II	Solution	LeetCode	Medium
116	Game Play Analysis IV	Solution	LeetCode	Medium
117	Number of Unique Subjects Taught by Each Teacher	Solution	LeetCode	Easy
118	User Activity for the Past 30 Days I	Solution	LeetCode	Easy	BETWEEN, DATE w/ INTERVAL
119	Product Sales Analysis III	Solution	LeetCode	Medium
120	Classes More Than 5 Students	Solution	LeetCode	Easy
121	Find Followers Count	Solution	LeetCode	Easy
122	Biggest Single Number	Solution	LeetCode	Easy
123	Customers Who Bought All Products	Solution	LeetCode	Medium
124	The Number of Employees Which Report to Each Employee	Solution	LeetCode	Easy
125	Primary Department for Each Employee	Solution	LeetCode	Easy	UNION
126	Triangle Judgement	Solution	LeetCode	Easy	Triangle Inequality Theorem
127	Consecutive Numbers	Solution	LeetCode	Medium
128	Last Person to Fit in the Bus	Solution	LeetCode	Medium	ORDER BY turn RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
129	Count Salary Categories	Solution	LeetCode	Medium	IFNULL
130	Employees Whose Manager Left the Company	Solution	LeetCode	Easy
131	Exchange Seats	Solution	LeetCode	Medium	CASE w/ LAG w/ COALESCE
132	Movie Rating	Solution	LeetCode	Medium
133	Restaurant Growth	Solution	LeetCode	Medium
134	Friend Requests II: Who Has the Most Friends	Solution	LeetCode	Medium	UNION ALL
135	Investments in 2016	Solution	LeetCode	Medium
136	Department Top Three Salaries	Solution	LeetCode	Hard
137	Fix Names in a Table	Solution	LeetCode	Easy	SUBSTRING()
138	Patients With a Condition	Solution	LeetCode	Easy
139	Second Highest Salary	Solution	LeetCode	Medium
140	Group Sold Products By The Date	Solution	DataLemur	Easy	GROUP_CONCAT()
141	List the Products Ordered in a Period	Solution	DataLemur	Easy
142	Product Price at a Given Date	Solution	LeetCode	Medium	UNION ALL, LEFT JOIN, Window Function
143	Rank Scores	Solution	LeetCode	Medium
144	Customers Who Never Order	Solution	LeetCode	Easy
145	Game Play Analysis I	Solution	LeetCode	Easy
146	Department Highest Salary	Solution	LeetCode	Medium
147	Customer Placing the Largest Number of Orders	Solution	LeetCode	Easy	ORDER BY w/ Aggregate Function
148	Swap Salary	Solution	LeetCode	Easy
149	Sales Person	Solution	LeetCode	Easy
150	Reformat Department Table	Solution	LeetCode	Easy
151	Capital Gain/Loss	Solution	LeetCode	Medium
152	Daily Leads and Partners	Solution	LeetCode	Easy
153	Find Total Time Spent by Each Employee	Solution	LeetCode	Easy
154	Find Users With Valid E-Mails	Solution	LeetCode	Easy	Regex
155	Actors and Directors Who Cooperated At Least Three Times	Solution	LeetCode	Easy
156	Top Travellers	Solution	LeetCode	Easy
157	Rearrange Products Table	Solution	LeetCode	Easy
158	Calculate Special Bonus	Solution	LeetCode	Easy
159	The Latest Login in 2020	Solution	LeetCode	Easy
160	Employees With Missing Information	Solution	LeetCode	Easy	FULL JOIN
161	Delete Duplicate Emails	Solution	LeetCode	Easy
162	Find Products with Valid Serial Numbers	Solution	LeetCode	Easy	Regex
163	DNA Pattern Recognition	Solution	LeetCode	Easy
164	Even or Odd	Solution	Codewars	Easy
165	Daily Hospital Admissions and Discharges Report	Solution	Codewars	Medium	TO_CHAR()
166	Films selection with a twist	Solution	Codewars	Medium	PERCENTILE_DISC
167	SQL Basics: Raise to the Power	Solution	Codewars	Easy	^ or POW()
168	SQL Basics: Simple UNION ALL	Solution	Codewars	Medium
169	NPV Queries	Solution	LeetCode	Easy
170	Game Play Analysis II	Solution	LeetCode	Easy
171	Total Traveled Distance	Solution	LeetCode	Easy
172	Create a Session Bar Chart	Solution	LeetCode	Easy	Histogram bins w/ UNION, CASE
173	Customers Who Bought Products A and B but Not C	Solution	LeetCode	Medium	STRING_AGG
174	Highest Salaries Difference	Solution	LeetCode	Easy
175	Form a Chemical Bond	Solution	LeetCode	Easy	CROSS JOIN
176	Concatenate the Name and the Profession	Solution	LeetCode	Easy	SUBSTRING vs LEFT
177	Consecutive Available Seats	Solution	LeetCode	Easy
178	Shortest Distance in a Line	Solution	LeetCode	Easy	SELF-JOIN vs CROSS JOIN
179	Friendly Movies Streamed Last Month	Solution	LeetCode	Easy
180	Books with NULL Ratings	Solution	LeetCode	Easy
181	Customer Order Frequency	Solution	LeetCode	Easy	USING, HAVING
182	Low-Quality Problems	Solution	LeetCode	Easy
183	Product Sales Analysis II	Solution	LeetCode	Easy
184	Project Employees II	Solution	LeetCode	Easy	Window function w/ GROUP BY
185	Sales Analysis I	Solution	LeetCode	Easy
186	Sales Analysis II	Solution	LeetCode	Easy	STRING_AGG vs GROUP_CONCAT
187	Sales Analysis III	Solution	LeetCode	Easy	HAVING
188	The Winner University	Solution	LeetCode	Easy
189	Unique Orders and Customers Per Month	Solution	LeetCode	Easy
190	Warehouse Manager	Solution	LeetCode	Easy
191	The Number of Rich Customers	Solution	LeetCode	Easy
192	Find Candidates for Data Scientist Position	Solution	LeetCode	Easy
193	Classifying Triangles by Lengths	Solution	LeetCode	Easy
194	Reported Posts	Solution	LeetCode	Easy
195	Game Play Analysis III	Solution	LeetCode	Medium
196	Winning Candidate	Solution	LeetCode	Medium
197	Get Highest Answer Rate Question	Solution	LeetCode	Medium
198	Count Student Number in Departments	Solution	LeetCode	Medium
199	Tree Node	Solution	LeetCode	Medium
200	Shortest Distance in a Plane	Solution	LeetCode	Medium	CROSS JOIN

Author(s)

• @ggeerraarrdd

License

• MIT License

Frontispiece

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.