dana3601
diff --git a/‎Customers or Users/# 1555. Bank Account Summary.sql
Lines changed: 24 additions & 8 deletions b/‎Customers or Users/# 1555. Bank Account Summary.sql
Lines changed: 24 additions & 8 deletions
diff --git a/‎Order and Deliver/# 1445. Apples & Oranges.sql
Lines changed: 15 additions & 0 deletions b/‎Order and Deliver/# 1445. Apples & Oranges.sql
Lines changed: 15 additions & 0 deletions
@@ -3,9 +3,31 @@
 # Find out the current balance of all users and 
 # check wheter they have breached their credit limit (If their current credit is less than 0).
 
+
+
+# First sulution is SUM the two types transaction in SELECT statement
+SELECT user_id,user_name,
+
+# use IFNULL( ,0) because it could be a situation that the customer has no transaction
+IFNULL(SUM(CASE WHEN a.user_id=b.paid_by THEN -amount ELSE amount END),0)+a.credit as credit,
+CASE WHEN IFNULL(SUM(CASE WHEN a.user_id=b.paid_by THEN -amount ELSE amount END),0)>=-a.credit THEN "No" ELSE "Yes" END as credit_limit_breached
+FROM Users as a
+LEFT JOIN Transactions as b
+
+# Becareful there is no user_id in Transactions table
+ON a.user_id=b.paid_by OR a.user_id=b.paid_to
+GROUP BY a.user_id;
+
+
+
+
+
+# Second sulution is SUM the two types transaction by making two extra tables
 SELECT u.user_id AS USER_ID, 
 u.user_name AS USER_NAME, 
 (u.credit - IFNULL(a.c_munis,0) + IFNULL(b.c_plus,0) )AS CREDIT,
+
+# 'Yes' means excessed (breached their credit limit)
 IF(u.credit - IFNULL(a.c_munis,0) + IFNULL(b.c_plus,0)<0,'Yes','No') AS CREDIT_LIMIT_BREACHED
 FROM users u
 LEFT JOIN
@@ -22,14 +44,6 @@ ON b.paid_to = u.user_id
 
 
 
-SELECT user_id,user_name,
-IFNULL(SUM(CASE WHEN a.user_id=b.paid_by THEN -amount ELSE amount END),0)+a.credit as credit,
-CASE WHEN IFNULL(SUM(CASE WHEN a.user_id=b.paid_by THEN -amount ELSE amount END),0)>=-a.credit THEN "No" ELSE "Yes" END as credit_limit_breached
-FROM Users as a
-LEFT JOIN Transactions as b
-ON a.user_id=b.paid_by OR a.user_id=b.paid_to
-GROUP BY a.user_id;
-
 
 
 with spent as (
@@ -57,6 +71,8 @@ order by user_id
 
 
 
+
+
 with cte as
 (select q.user_id, sum(q.net_amt) as trans_total from
 (select paid_by as user_id, -1*sum(amount) as net_amt
 
@@ -3,13 +3,20 @@
 # Report the difference between number of apples and oranges sold each day
 # Return the result table ordered by sale_date in format ('YYYY-MM-DD')
 
+
+# If there only two possible situations in CASE statement, 
+# I can use ELSE for another situation, instead of stating both of two situations.
 SELECT sale_date, 
     SUM(CASE WHEN fruit = "apples" THEN sold_num ELSE (-sold_num) END) AS diff
 FROM Sales
+
+# Don't forgot to GROUP BY when using SUM
 GROUP BY sale_date
 
 
 
+
+
 SELECT a.sale_date sale_date, num_apples - num_oranges as diff
 FROM
 	# Create table for Apples
@@ -63,6 +70,14 @@ on a.sale_date=o.sale_date
 
 
 
+with tmp as 
+(select sale_date,
+sum(case when fruit = 'apples' then sold_num end) as apples,
+sum(case when fruit = 'oranges' then sold_num end) as oranges
+from sales
+group by sale_date)
+select sale_Date, (apples-oranges) as diff
+from tmp