SQL Questions & Solutions

Day	Source	My Solution	Link	Remarks
001	DataLemur	Data Science Skills	Link 🔗
	DataLemur	Page With No Likes	Link 🔗
002	DataLemur	Unfinished Parts	Link 🔗
003	DataLemur	Laptop vs Mobile Viewership	Link 🔗	Case when
	DataLemur	Cities with Completed Trades	Link 🔗	Joins, order of execution
004	DataLemur	Duplicate Job Listings	Link 🔗	CTE
	DataLemur	Final Account Balance	Link 🔗	CTE
	DataLemur	Histogram of Tweets	Link 🔗
005	DataLemur	User's Third Transaction	Link 🔗	window functions with partition by, over by
006	DataLemur	Compensation Outliers	Link 🔗	use CASE WHEN result in WHERE clause with CTE/subquery
007	DataLemur	Sending vs. Opening Snaps	Link 🔗	aggregation x2
008	DataLemur	Tweets' Rolling Averages	Link 🔗	Rollmean
009	DataLemur	Odd and Even Measurements	Link 🔗	window function (ROW_NUMBER)
010	DataLemur	Highest-Grossing Items	Link 🔗	First n rows, grouped
011	DataLemur	First Transaction	Link 🔗	First row in each group with ROW_NUMBER()
012	DataLemur	Linkedin Power Creators (Part 1)	Link 🔗	joins
	DataLemur	Linkedin Power Creators (Part 2)	Link 🔗	HAVING
013	DataLemur	Top 5 Artists	Link 🔗	DENSE_RANK(), multiple CTEs, using aggregated columns in LIMIT
014	DataLemur	Signup Confirmation Rate	Link 🔗	JOIN with AND, integer divisions in postgres discards fractional part, ::DECIMAL
015	DataLemur	Fill Missing Client Data	Link 🔗	select the previous non-null value; OVER (ORDER BY ...); COUNT ignores NULL
016	DataLemur	Consulting Bench Time	Link 🔗	date difference
017	DataLemur	Spotify Streaming History	Link 🔗	UNION removes duplicates while UNION ALL does not; datetype casting when comparing datetimes
018	DataLemur	Histogram of Users and Purchases	Link 🔗
019	DataLemur	Unique Money Transfer Relationships	Link 🔗	INTERESECT on two selections (columns flipped) returns unique pairs twice, useful for counting the number of rows. Use DISTINCT and CASE WHEN to return the exact list of unique pairs.
020	DataLemur	Pharmacy Analytics (Part 4)	Link 🔗	First n rows by group, with ROW_NUMBER()
021	DataLemur	Repeat Purchases on Multiple Days	Link 🔗	HAVING COUNT(DISTINCT Column) ...
022	DataLemur	Mean, Median, Mode	Link 🔗	Mean with AVG(), Median with PERCENTIL_CONT(0.5), Mode with MODE() and the WITHIN GROUP (ORDER BY variable) clause for aggregation
023	DataLemur	Supercloud Customer	Link 🔗	HAVING COUNT(DISTINCT column)
024	DataLemur	Compressed Mode	Link 🔗	Subsquery within a WHERE clause