Michael Lam

  SELECT v.customer_id as customer_id , COUNT(1) as count_no_trans FROM Visits v WHERE NOT EXISTS ( SELECT 1 FROM Transactions T WHERE V.VISIT_ID = T.VISIT_ID ) GROUP BY v.customer_id Input: Visits +----------+-------------+ | visit_id | customer_id | +----------+-------------+ | 1 | 23 | | 2 | 9 | | 4 | 30 | | 5 | 54 | | 6 | 96 | | 7 | 54 | | 8 | 54 | +----------+-------------+ Transactions +----------------+----------+--------+ | transaction_id | visit_id | amount | +----------------+----------+--------+ | 2 | 5 | 310 | | 3 | 5 | 300 | | 9 | 5 | 200 | | 12 | 1 | 910 | | 13 | 2 | 970 | +----------------+----------+--------+ Output: +-------------+----------------+ | customer_id | count_no_trans | +-------------+----------------+ | 54 ...

  SELECT email as Email From Person Group By email HAVING COUNT (email) > 1. // << true of false also can multiple HAVING XXX(BBB) > CC AND ZZZ(FFF) = RR... Input: Person table: +----+---------+ | id | email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ Output: +---------+ | Email | +---------+ | a@b.com | +---------+ Explanation: a@b.com is repeated two times.

SELECT sell_table.stock_name,(sell_table.total_sell - buy_table.total_buy) AS capital_gain_loss FROM ( SELECT SUM (price)as total_sell,stock_name FROM Stocks WHERE operation = 'Sell' GROUP BY stock_name) sell_table , ( SELECT SUM (price)as total_buy ,stock_name FROM Stocks WHERE operation = 'Buy' GROUP BY stock_name) buy_table WHERE sell_table.stock_name = buy_table.stock_name GROUP BY stock_name; Input: Stocks table: +---------------+-----------+---------------+--------+ | stock_name | operation | operation_day | price | +---------------+-----------+---------------+--------+ | Leetcode | Buy | 1 | 1000 | | Corona Masks | Buy | 2 | 10 | | Leetcode | Sell | 5 | 9000 | | Handbags | Buy | 17 | 30000 | | Corona Masks | Sell | 3 | 1010 | | Corona Masks | Buy | 4 | 1000 | | Corona Masks | Sell | 5 | 500 ...

Use all the result form left side even that are null at the second table. Will return null when can t find result in right table. SELECT p.firstName , p.lastName, a.city , a.state FROM Person p LEFT JOIN Address a ON p.personId = a.personId; //USING(persionId) < Also OK Input: Person table: +----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ | 1 | Wang | Allen | | 2 | Alice | Bob | +----------+----------+-----------+ Address table: +-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ | 1 | 2 | New York City | New York | | 2 | 3 | Leetcode | California | +-----------+----------+---------------+------------+ Output: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+-------...

For example :   A = 1, Z = 26 , Find AB =? YZ = 25*(26^1) + 26*(26^0) XYZ = 24*(26^2) + 25*(26^1) + 26*(26^0) class Solution { public int titleToNumber ( String columnTitle ) { int result = 0 ; for ( int i = 0 ;i < columnTitle . length ();i++){ result += ( columnTitle . charAt (i) - 64 ) * Math . pow ( 26 , columnTitle . length () - i - 1 ); } return result; } }  

  class Solution { public void moveZeroes ( int [ ] nums ) { int remove = 0 ; for ( int i = 0 , j = 0 ; j < nums . length ; j ++ ) { if ( nums [ j ] != remove ) { int swap = nums [ i ] ; nums [ i ] = nums [ j ] ; nums [ j ] = swap ; i ++ ; } } return ; } }