Adding solutions

cM2908 · cM2908 · commit 791cac307e4a · 2022-11-24T10:50:27.000+05:30
diff --git a/dump_file/leetcodedb.sql b/dump_file/leetcodedb.sql
diff --git a/easy/1294. Weather Type in Each Country (Easy).sql b/easy/1294. Weather Type in Each Country (Easy).sql
@@ -0,0 +1,10 @@
+SELECT country_name,
+	CASE WHEN AVG(weather_state) <= 15 THEN 'Cold'
+		 WHEN AVG(weather_state) >= 25 THEN 'Hot'
+		 ELSE 'Warm'
+	END AS weather_type
+FROM weather_1294 w
+INNER JOIN countries_1294 c ON w.country_id = c.country_id
+WHERE EXTRACT(month FROM day) = 11
+GROUP BY country_name;
+
diff --git a/easy/1303. Find the Team Size (Easy).sql b/easy/1303. Find the Team Size (Easy).sql
@@ -0,0 +1,4 @@
+SELECT employee_id,
+	COUNT(employee_id) OVER (PARTITION BY team_id) AS team_size
+FROM employee_1303
+ORDER BY employee_id;
diff --git a/easy/1322. Ads Performance (Easy).sql b/easy/1322. Ads Performance (Easy).sql
@@ -0,0 +1,9 @@
+SELECT ad_id,
+	COALESCE(ROUND(AVG(
+	CASE WHEN action = 'Clicked' THEN 1
+	     WHEN action = 'Viewed' THEN 0
+	     ELSE NULL 
+	END)*100,2),0.00) AS ctr
+FROM ads_1322
+GROUP BY ad_id
+ORDER BY ctr DESC;
diff --git a/easy/1327. List the Products Ordered in a Period (Easy).sql b/easy/1327. List the Products Ordered in a Period (Easy).sql
@@ -0,0 +1,7 @@
+SELECT p.product_name,SUM(o.unit)
+FROM orders_1327 o
+INNER JOIN products_1327 p ON o.product_id = p.product_id
+WHERE DATE_TRUNC('MONTH',o.order_date)::DATE = '2020-02-01'
+GROUP BY p.product_name
+HAVING SUM(o.unit) >= 100;
+
diff --git a/easy/1350. Students With Invalid Departments (Easy).sql b/easy/1350. Students With Invalid Departments (Easy).sql
@@ -0,0 +1,4 @@
+SELECT s.id,s.name
+FROM students_1350 s
+LEFT JOIN departments_1350 d ON s.department_id = d.id
+WHERE d.id IS NULL;
diff --git a/easy/1378. Replace Employee ID With The Unique Identifier (Easy).sql b/easy/1378. Replace Employee ID With The Unique Identifier (Easy).sql
@@ -0,0 +1,4 @@
+SELECT eu.unique_id,e.name
+FROM employee_1378 e
+LEFT JOIN employee_uni_1378 eu ON e.id = eu.id
+ORDER BY e.name;
diff --git a/easy/1407. Top Travellers (Easy).sql b/easy/1407. Top Travellers (Easy).sql
@@ -0,0 +1,5 @@
+SELECT u.name,COALESCE(SUM(r.distance),0) AS travelled_distance
+FROM users_1407 u
+LEFT JOIN rides_1407 r ON u.id = r.user_id
+GROUP BY u.name
+ORDER BY travelled_distance DESC,u.name;
diff --git a/easy/1435. Create a Session Bar Chart (Easy).sql b/easy/1435. Create a Session Bar Chart (Easy).sql
@@ -0,0 +1,15 @@
+WITH bins AS (
+    SELECT '[0-5>' AS bin,  0 AS min_duration, 5*60 AS max_duration
+    UNION ALL
+    SELECT '[5-10>' AS bin,  5*60 AS min_duration, 10*60 AS max_duration
+    UNION ALL
+    SELECT '[10-15>' AS bin, 10*60 AS min_duration, 15*60 AS max_duration
+    UNION ALL
+    SELECT '15 or more' AS bin,  15*60 as min_duration, 2147483647 AS max_duration
+)
+SELECT b.bin, COUNT(s.session_id) AS total
+FROM bins b
+LEFT JOIN sessions_1435 s
+		ON s.duration >= min_duration 
+        AND s.duration < max_duration				 
+GROUP BY b.bin;
diff --git a/easy/1484. Group Sold Products By The Date (Easy).sql b/easy/1484. Group Sold Products By The Date (Easy).sql
@@ -0,0 +1,4 @@
+SELECT sell_date,COUNT(DISTINCT product) AS num_sold,STRING_AGG(DISTINCT product,',' ORDER BY product) AS products
+FROM activities_1484
+GROUP BY sell_date
+ORDER BY sell_date;
diff --git a/easy/1495. Friendly Movies Streamed Last Month (Easy).sql b/easy/1495. Friendly Movies Streamed Last Month (Easy).sql
@@ -0,0 +1,6 @@
+SELECT DISTINCT c.title
+FROM tv_program_1495 t
+INNER JOIN content_1495 c 
+ON t.content_id = c.content_id AND 
+	c.kids_content = 'Y' AND 
+	DATE_TRUNC('MONTH',t.program_date)::DATE = '2020-06-01';
diff --git a/easy/1511. Customer Order Frequency (Easy).sql b/easy/1511. Customer Order Frequency (Easy).sql
@@ -0,0 +1,15 @@
+WITH par_spent AS (
+	SELECT c.name,DATE_TRUNC('MONTH',o.order_date)::DATE,SUM(quantity*price) AS spent
+	FROM orders_1511 o
+	INNER JOIN product_1511 p
+	ON o.product_id = p.product_id AND 
+	(DATE_TRUNC('MONTH',o.order_date)::DATE = '2020-06-01' OR DATE_TRUNC('MONTH',o.order_date)::DATE = '2020-07-01')
+	INNER JOIN customers_1511 c
+	ON c.customer_id = o.customer_id
+	GROUP BY c.name,DATE_TRUNC('MONTH',o.order_date)::DATE
+	HAVING SUM(quantity*price)>=100
+)
+SELECT name
+FROM par_spent
+GROUP BY name
+HAVING COUNT(name) = 2;
diff --git a/easy/1517. Find Users With Valid E-Mails (Easy).sql b/easy/1517. Find Users With Valid E-Mails (Easy).sql
@@ -0,0 +1,3 @@
+SELECT *
+FROM users_1517
+WHERE mail SIMILAR TO '[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com';
diff --git a/easy/1527. Patients With a Condition (Easy).sql b/easy/1527. Patients With a Condition (Easy).sql
@@ -0,0 +1,3 @@
+SELECT *
+FROM patients_1527
+WHERE conditions LIKE '% DIAB1%' OR conditions LIKE 'DIAB1%';
diff --git a/easy/1543. Fix Product Name Format (Easy).sql b/easy/1543. Fix Product Name Format (Easy).sql
@@ -0,0 +1,4 @@
+SELECT LOWER(TRIM(product_name)) AS product_name,TO_CHAR(sale_date,'YYYY-MM') AS sale_date,COUNT(sale_id) AS total
+FROM sales_1543
+GROUP BY LOWER(TRIM(product_name)),TO_CHAR(sale_date,'YYYY-MM')
+ORDER BY product_name,sale_date;
diff --git a/easy/1565. Unique Orders and Customers Per Month (Easy).sql b/easy/1565. Unique Orders and Customers Per Month (Easy).sql
@@ -0,0 +1,6 @@
+SELECT TO_CHAR(order_date,'YYYY-MM') AS month,
+	COUNT(order_id) AS order_count,
+	COUNT(DISTINCT customer_id) AS customer_count
+FROM orders_1565
+WHERE invoice > 20
+GROUP BY TO_CHAR(order_date,'YYYY-MM');
diff --git a/easy/1571. Warehouse Manager (Easy).sql b/easy/1571. Warehouse Manager (Easy).sql
@@ -0,0 +1,4 @@
+SELECT w.name AS warehouse_name,SUM(p.width*p.height*p.length*w.units) AS volume
+FROM warehouse_1571 w
+INNER JOIN products_1571 p ON w.product_id = p.product_id
+GROUP BY w.name;
