Programming Problems

Practicing programming problems logging my solutions • JavaScript, SQL, Python & TypeScript <Updating>

• LeetCode
• PostgreSQL Exercises

LeetCode

• Algorithms
• Database

Algorithms

#	Title	Solutions	Time	Space	Difficulty	Tags
1	Two Sum	JavaScript Python	O(n)	O(n)	🟩 Easy	Array Hash Table
53	Maximum Subarray	JavaScript Python	O(n)	O(1)	🟨 Medium	Array Divide and Conquer Dynamic Programming
136	Single Number	JavaScript	O(n)	O(1)	🟩 Easy	Array Bit Manipulation
152	Maximum Product Subarray	Python	O(n)	O(1)	🟨 Medium	Array Dynamic Programming
171	Excel Sheet Column Number	JavaScript	O(n)	O(1)	🟩 Easy	Math String
217	Contains Duplicate	JavaScript Python	O(n)	O(n)	🟩 Easy	Array Hash Table Sorting
383	Ransom Note	JavaScript	O(m)	O(1)	🟩 Easy	Hash Table String Counting
412	Fizz Buzz	JavaScript	O(n)	O(n)	🟩 Easy	Math String Simulation
876	Middle of the Linked List	JavaScript	O(n)	O(1)	🟩 Easy	Linked List Two Pointers
1108	Defanging an IP Address	JavaScript Python	O(n)	O(1)	🟩 Easy	String
1119	Remove Vowels from a String	JavaScript	O(n)	O(1)	🟩 Easy	String
1342	Number of Steps to Reduce a Number to Zero	JavaScript TypeScript	O(log n)	O(1)	🟩 Easy	Math Bit Manipulation
1480	Running Sum of 1d Array	JavaScript	O(n)	O(1)	🟩 Easy	Array Prefix Sum
1672	Richest Customer Wealth	JavaScript	O(mn)	O(1)	🟩 Easy	Array Matrix
1678	Goal Parser Interpretation	JavaScript	O(n)	O(n)	🟩 Easy	String
1920	Build Array from Permutation	JavaScript Python	O(n)	O(1)	🟩 Easy	Array Simulation
1929	Concatenation of Array	JavaScript	O(n)	O(1)	🟩 Easy	Array
2011	Final Value of Variable After Performing Operations	JavaScript	O(n)	O(1)	🟩 Easy	Array String Simulation
2235	Add Two Integers	JavaScript Python	O(1)	O(1)	🟩 Easy	Math
2469	Convert the Temperature	JavaScript	O(1)	O(1)	🟩 Easy	Math

PostgreSQL Exercises

• Simple SQL Queries
• Joins and Subqueries
• Modifying data
• Aggregation
• Working with Timestamps
• String Operations
• Recursive Queries

Simple SQL Queries

#	Title	Solution	Tags
1	Retrieve everything from a table	SQL	SELECT *
2	Retrieve specific columns from a table	SQL	SELECT
3	Control which rows are retrieved	SQL	WHERE
4	Control which rows are retrieved - part 2	SQL	WHERE AND
5	Basic string searches	SQL	WHERE LIKE
6	Matching against multiple possible values	SQL	WHERE IN
7	Classify results into buckets	SQL	CASE AS
8	Working with dates	SQL	SQL Timestamp Format
9	Removing duplicates and ordering results	SQL	DISTINCT ORDER BY LIMIT
10	Combining results from multiple queries	SQL	UNION
11	Simple aggregation	SQL	MAX
12	More aggregation	SQL	Subquery Scalar Table

Joins and Subqueries

#	Title	Solution	Tags
1	Retrieve the start times of members' bookings	SQL	Implicit / Explicit INNER JOIN
2	Work out the start times of bookings for tennis courts	SQL	INNER JOIN IN / LIKE % Wildcard
3	Produce a list of all members who have recommended another member	SQL	Implicit / Explicit INNER JOIN Subquery
4	Produce a list of all members, along with their recommender	SQL	LEFT OUTER JOIN
5	Produce a list of all members who have used a tennis court	SQL	CONCAT_WS() || String Concatenation Operator INNER JOIN
6	Produce a list of costly bookings	SQL	CASE INNER JOIN
7	Produce a list of all members, along with their recommender, using no joins	SQL	Correlated Subquery
8	Produce a list of costly bookings, using a subquery	SQL	Subquery INNER JOIN

Modifying data

#	Title	Solution	Tags
1	Insert some data into a table	SQL	INSERT INTO ... VALUES
2	Insert multiple rows of data into a table	SQL	INSERT INTO ... SELECT UNION ALL
3	Insert calculated data into a table	SQL	INSERT INTO ... SELECT
4	Update some existing data	SQL	UPDATE
5	Update multiple rows and columns at the same time	SQL	UPDATE
6	Update a row based on the contents of another row	SQL	UPDATE ... FROM Subquery
7	Delete all bookings	SQL	DELETE TRUNCATE
8	Delete a member from the cd.members table	SQL	DELETE
9	Delete based on a subquery	SQL	DELETE Subquery Correlated Subquery

Aggregation

#	Title	Solution	Tags
1	Count the number of facilities	SQL	COUNT
2	Count the number of expensive facilities	SQL	COUNT
3	Count the number of recommendations each member makes	SQL	COUNT GROUP BY
4	List the total slots booked per facility	SQL	SUM
5	List the total slots booked per facility in a given month	SQL	SUM
6	List the total slots booked per facility per month	SQL	SUM EXTRACT
7	Find the count of members who have made at least one booking	SQL	COUNT(DISTINCT)
8	List facilities with more than 1000 slots booked	SQL	SUM HAVING
9	Find the total revenue of each facility	SQL	SUM(CASE)
10	Find facilities with a total revenue less than 1000	SQL	SELECT ... FROM SELECT SUM(CASE)
11	Output the facility id that has the highest number of slots booked	SQL	CTEs
12	List the total slots booked per facility per month, part 2	SQL	ROLLUP CUBE
13	List the total hours booked per named facility	SQL	TO_CHAR TRIM
14	List each member's first booking after September 1st 2012	SQL	MIN NOW
15	Produce a list of member names, with each row containing the total member count	SQL	Window Function OVER
16	Produce a numbered list of members	SQL	Window Function ROW_NUMBER
17	Output the facility id that has the highest number of slots booked, again	SQL	Window Function RANK
18	Rank members by (rounded) hours used	SQL	Window Function Arithmetic Rounding RANK
19	Find the top three revenue generating facilities	SQL	Window Function RANK CASE
20	Classify facilities by value	SQL	Window Function NTILE CASE
21	Calculate the payback time for each facility	SQL	Subquery CASE SUM
22	Calculate a rolling average of total revenue	SQL	VIEW RANGE PRECEDING GENERATE_SERIES INTERVAL CAST

Working with Timestamps

#	Title	Solution	Tags
1	Produce a timestamp for 1 a.m. on the 31st of August 2012	SQL	TIMESTAMP :: Operator CAST
2	Subtract timestamps from each other	SQL	INTERVAL
3	Generate a list of all the dates in October 2012	SQL	GENERATE_SERIES
4	Get the day of the month from a timestamp	SQL	EXTRACT
5	Work out the number of seconds between timestamps	SQL	EXTRACT(EPOCH)
6	Work out the number of days in each month of 2012	SQL	GENERATE_SERIES(TIMESTAMP) EXTRACT
7	Work out the number of days remaining in the month	SQL	DATE_TRUNC
8	Work out the end time of bookings	SQL	INTERVAL Multiplication LIMIT
9	Return a count of bookings for each month	SQL	DATE_TRUNC COUNT
10	Work out the utilisation percentage for each facility by month	SQL	Subquery ROUND EXTRACT(EPOCH)

String Operations

#	Title	Solution	Tags
1	Format the names of members	SQL	|| String Concatenation Operator CONCAT
2	Find facilities by a name prefix	SQL	LIKE % _
3	Perform a case-insensitive search	SQL	ILIKE CREATE INDEX UPPER
4	Find telephone numbers with parentheses	SQL	SIMILAR TO ~ Operator Regex POSITION
5	Pad zip codes with leading zeroes	SQL	LPAD / RPAD TEXT CHAR
6	Count the number of members whose surname starts with each letter of the alphabet	SQL	SUBSTRING SUBSTR LEFT
7	Clean up telephone numbers	SQL	REGEXP_REPLACE TRANSLATE

Recursive Queries

#	Title	Solution	Tags
1	Find the upward recommendation chain for member ID 27	SQL	WITH RECURSIVE UNION ALL INNER JOIN
2	Find the downward recommendation chain for member ID 1	SQL	WITH RECURSIVE UNION ALL INNER JOIN
3	Produce a CTE that can return the upward recommendation chain for any member	SQL	WITH RECURSIVE UNION ALL INNER JOIN OR