Adding Solutions

cM2908 · cM2908 · commit bf5e444a67af · 2022-11-25T12:28:47.000+05:30
diff --git a/README.md b/README.md
@@ -1,7 +1,7 @@
 # Leetcode SQL Questions & Solutions <br/>
 #### Repository Contains :<br/>
 
-(1) 160+ Leetcode SQL Question Solutions<br/>
+(1) 190+ Leetcode SQL Question Solutions<br/>
 (2) PostgreSQL Dump File (leetcodedb.sql)<br/>
 
 #### Problem statements of all questions including leetcode premium questions :<br/>
diff --git a/dump_file/leetcodedb.sql b/dump_file/leetcodedb.sql
diff --git a/easy/1581. Customer Who Visited but Did Not Make Any Transactions (Easy).sql b/easy/1581. Customer Who Visited but Did Not Make Any Transactions (Easy).sql
@@ -0,0 +1,6 @@
+SELECT v.customer_id,COUNT(v.visit_id)
+FROM visits_1581 v
+LEFT JOIN transactions_1581 t ON v.visit_id = t.visit_id
+WHERE t.transaction_id IS NULL
+GROUP BY v.customer_id
+ORDER BY v.customer_id;
diff --git a/easy/1587. Bank Account Summary II (Easy).sql b/easy/1587. Bank Account Summary II (Easy).sql
@@ -0,0 +1,6 @@
+SELECT u.name,SUM(t.amount)
+FROM transactions_1587 t
+INNER JOIN users_1587 u ON t.account=u.account
+GROUP BY u.name
+HAVING SUM(t.amount) > 10000
+
diff --git a/easy/1607. Sellers With No Sales (Easy).sql b/easy/1607. Sellers With No Sales (Easy).sql
@@ -0,0 +1,6 @@
+SELECT seller_name
+FROM seller_1607 
+WHERE seller_id NOT IN 
+(SELECT DISTINCT seller_id
+FROM orders_1607
+WHERE EXTRACT(YEAR FROM sale_date) = 2020);
diff --git a/easy/1623. All Valid Triplets That Can Represent a Country (Easy).sql b/easy/1623. All Valid Triplets That Can Represent a Country (Easy).sql
@@ -0,0 +1,6 @@
+SELECT a.student_name,b.student_name,c.student_name
+FROM school_a_1623 a
+CROSS JOIN school_b_1623 b
+CROSS JOIN school_c_1623 c
+WHERE a.student_id <> b.student_id AND a.student_id <> c.student_id AND b.student_id <> c.student_id AND
+	a.student_name <> b.student_name AND a.student_name <> c.student_name AND b.student_name <> c.student_name;
diff --git a/easy/1633. Percentage of Users Attended a Contest (Easy).sql b/easy/1633. Percentage of Users Attended a Contest (Easy).sql
@@ -0,0 +1,12 @@
+SELECT contest_id,ROUND((COUNT(DISTINCT user_id)*100.0)/user_count.cnt,2) AS percentage
+FROM register_1633
+CROSS JOIN (SELECT COUNT(*) AS cnt FROM users_1633) user_count
+GROUP BY contest_id,user_count.cnt
+ORDER BY percentage DESC,contest_id;
+
+--OR--
+
+SELECT contest_id,ROUND((COUNT(DISTINCT user_id)*100.0)/(SELECT COUNT(*) AS cnt FROM users_1633),2) AS percentage
+FROM register_1633
+GROUP BY contest_id
+ORDER BY percentage DESC,contest_id;
diff --git a/easy/1661. Average Time of Process per Machine (Easy).sql b/easy/1661. Average Time of Process per Machine (Easy).sql
@@ -0,0 +1,6 @@
+SELECT s.machine_id,ROUND(AVG(e.timestamp-s.timestamp)::NUMERIC,3) AS processing_time
+FROM activity_1661 s
+INNER JOIN activity_1661 e 
+ON s.activity_type = 'start' AND e.activity_type = 'end' AND
+	s.machine_id = e.machine_id AND s.process_id = e.process_id
+GROUP BY s.machine_id;
diff --git a/easy/1667. Fix Names in a Table (Easy).sql b/easy/1667. Fix Names in a Table (Easy).sql
@@ -0,0 +1,3 @@
+SELECT user_id,INITCAP(name)
+FROM users_1667
+ORDER BY user_id;
diff --git a/easy/1677. Product’s Worth Over Invoices (Easy).sql b/easy/1677. Product’s Worth Over Invoices (Easy).sql
@@ -0,0 +1,5 @@
+SELECT p.name,SUM(i.rest) AS rest,SUM(i.paid) AS paid,SUM(i.canceled) AS canceled,SUM(i.refunded) AS refunded
+FROM invoice_1677 i
+INNER JOIN product_1677 p ON i.product_id = p.product_id
+GROUP BY p.name
+ORDER BY p.name;
diff --git a/easy/1683. Invalid Tweets (Easy).sql b/easy/1683. Invalid Tweets (Easy).sql
@@ -0,0 +1,3 @@
+SELECT tweet_id
+FROM tweets_1683
+WHERE LENGTH(content)>15;
diff --git a/easy/1693. Daily Leads and Partners (Easy).sql b/easy/1693. Daily Leads and Partners (Easy).sql
@@ -0,0 +1,3 @@
+SELECT date_id,make_name,COUNT(DISTINCT lead_id) AS unique_leads,COUNT(DISTINCT partner_id) AS unique_partners
+FROM daily_sales_1693
+GROUP BY date_id,make_name;
diff --git a/easy/1729. Find Followers Count (Easy).sql b/easy/1729. Find Followers Count (Easy).sql
@@ -0,0 +1,4 @@
+SELECT user_id,COUNT(follower_id) AS follower_count
+FROM followers_1729
+GROUP BY user_id
+ORDER BY user_id;
diff --git a/easy/1731. The Number of Employees Which Report to Each Employee (Easy).sql b/easy/1731. The Number of Employees Which Report to Each Employee (Easy).sql
@@ -0,0 +1,5 @@
+SELECT mgr.employee_id,mgr.name,COUNT(emp.name) AS reports_count,ROUND(AVG(emp.age)) AS average_age
+FROM employees_1731 mgr
+INNER JOIN employees_1731 emp ON mgr.employee_id = emp.reports_to
+GROUP BY mgr.employee_id,mgr.name
+ORDER BY mgr.employee_id;
diff --git a/easy/1741. Find Total Time Spent by Each Employee (Easy).sql b/easy/1741. Find Total Time Spent by Each Employee (Easy).sql
@@ -0,0 +1,4 @@
+SELECT event_day AS day,emp_id,SUM(out_time-in_time) AS total_time
+FROM employees_1741
+GROUP BY event_day,emp_id
+ORDER BY day,emp_id;
diff --git a/easy/1757. Recyclable and Low Fat Products (Easy).sql b/easy/1757. Recyclable and Low Fat Products (Easy).sql
@@ -0,0 +1,3 @@
+SELECT product_id
+FROM products_1757
+WHERE low_fats = 'Y' AND recyclable = 'Y';
diff --git a/easy/1777. Product’s Price for Each Store (Easy).sql b/easy/1777. Product’s Price for Each Store (Easy).sql
@@ -0,0 +1,38 @@
+CREATE OR REPLACE FUNCTION pivot_products_1777()
+RETURNS TEXT
+LANGUAGE PLPGSQL
+AS
+$$
+DECLARE
+	stores_array TEXT[];
+	store_name TEXT;
+	query_text TEXT := '';
+BEGIN
+	SELECT ARRAY_AGG(DISTINCT store ORDER BY store ASC)
+	INTO stores_array
+	FROM products_1777;
+	
+	query_text := query_text || 'SELECT product_id, ';
+	FOREACH store_name IN ARRAY stores_array
+	LOOP
+		query_text := query_text || 'SUM(CASE WHEN store = ''' || store_name || ''' THEN price ELSE NULL END) AS "' || store_name || '",';
+	END LOOP;
+	query_text := LEFT(query_text,LENGTH(query_text)-1);
+	
+	query_text := query_text || ' FROM products_1777 GROUP BY product_id ORDER BY product_id;';
+	
+	RETURN query_text;
+END
+$$;
+
+SELECT pivot_products_1777();
+
+/*
+SELECT product_id, 
+	SUM(CASE WHEN store = 'store1' THEN price ELSE NULL END) AS "store1",
+	SUM(CASE WHEN store = 'store2' THEN price ELSE NULL END) AS "store2",
+	SUM(CASE WHEN store = 'store3' THEN price ELSE NULL END) AS "store3" 
+FROM products_1777 
+GROUP BY product_id 
+ORDER BY product_id;
+*/
diff --git a/easy/1789. Primary Department for Each Employee (Easy).sql b/easy/1789. Primary Department for Each Employee (Easy).sql
@@ -0,0 +1,8 @@
+WITH employee_departments AS (
+	SELECT *,
+		COUNT(employee_id) OVER (PARTITION BY employee_id) AS cnt
+	FROM employee_1789
+)
+SELECT employee_id,department_id
+FROM employee_departments
+WHERE primary_flag = 'Y' OR (primary_flag = 'N' AND cnt = 1);

Original file line number	Diff line number	Diff line change
`@@ -0,0 +1,3 @@`
	`1`	`+SELECT user_id,INITCAP(name)`
	`2`	`+FROM users_1667`
	`3`	`+ORDER BY user_id;`