Friday, June 10, 2016

LeetCode Database



https://leetcode.com/problems/find-median-given-frequency-of-numbers/#/description
The Numbers table keeps the value of number and its frequency.
+----------+-------------+
|  Number  |  Frequency  |
+----------+-------------|
|  0       |  7          |
|  1       |  1          |
|  2       |  3          |
|  3       |  1          |
+----------+-------------+
In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0) / 2 = 0.


Write a query to find the median of all numbers and name the result as median.

https://discuss.leetcode.com/topic/167/managers-with-at-least-5-direct-reports
Given an employee table, find all managers with at least 5 direct reports.
IdNameDepartmentManagerId
101JohnAnull
102DanA101
103JamesA101
104AmyA101
105AnneA101
106RonB101

For the above table, your SQL query should return "John".
SELECT Name FROM Employee AS e INNER JOIN (SELECT ManagerId as m, COUNT(id) as c FROM Employee GROUP BY ManagerId HAVING c>=5) as e2
ON e.id = e2.m

https://leetcode.com/problems/delete-duplicate-emails/
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
http://dartmooryao.blogspot.com/2016/06/leetcode-196-delete-duplicate-emails.html
Idea 1:
Select the record with min value
and delete other records (Use "not in")

delete from Person
where Id not in
(select Id from
 (select min(Id) as Id from Person group by Email) p );

Idea 2:
Join the same table
Delete the record from one of the table that have larger Id and the same email

delete p1 from
Person p1, Person p2
where p1.Email = p2.Email
and p1.Id > p2.Id
https://leetcode.com/problems/rising-temperature/
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+
For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+
http://dartmooryao.blogspot.com/2016/06/leetcode-197-rising-temperature.html
Join the same table, and use "DateDiff" to calculate the two dates.
Note that "DateDiff" return positive value when the first date is larger, otherwise negative number.

select distinct w1.Id
from Weather w1, Weather w2
where DateDiff(w1.Date, w2.Date) = 1
and w1.Temperature > w2.Temperature

https://leetcode.com/problems/nth-highest-salary/
Write a SQL query to get the nth highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+


For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
http://dartmooryao.blogspot.com/2016/06/leetcode-177-nth-highest-salary.html
(1) Use Offset again
(2) Since offset number is N-1, and offset value cannot be a math function (such as n-1), we have to minus n before we use in the offset value

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee
      order by Salary desc
      limit 1 offset N
  );
END
https://leetcode.com/problems/second-highest-salary/
Write a SQL query to get the second highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.
http://dartmooryao.blogspot.com/2016/06/leetcode-176-second-highest-salary.html
Better solution: use "offset"!!
(1) The column name must be "SecondHighestSalary"
(2) It's required to return "null" when it's empty, so we union it with null

select distinct Salary as SecondHighestSalary from Employee
union select null as SecondHighestSalary
order by SecondHighestSalary desc
limit 1 offset 1

https://www.hackerrank.com/challenges/weather-observation-station-20






median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to  decimal places.
Note: Oracle solutions are not permitted for this challenge.
Input Format
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.
http://dartmooryao.blogspot.com/2016/06/hakerrank-sql-weather-observation.html
calculate the index of two possible median

set @mid1 := (select round((count(*)+1)/2) from station);
set @mid2 := (select round(count(*)/2) from station);
set @rowNo = 0;

select round(avg(lat_n),4) from
(select @rowNo:=@rowNo+1 as rowNo, lat_n from station order by lat_n) t
where rowNo = @mid1 or rowNo = @mid2;


==============================
The idea here is to count the number of larger and smaller than the target, and use it in the where condition!!

select round(lat_n,4) from station s
where (select count(lat_n) from station where lat_n>s.lat_n)
= (select count(lat_n) from station where lat_n<s.lat_n);

Labels

Review (572) System Design (334) System Design - Review (198) Java (189) Coding (75) Interview-System Design (65) Interview (63) Book Notes (59) Coding - Review (59) to-do (45) Linux (43) Knowledge (39) Interview-Java (35) Knowledge - Review (32) Database (31) Design Patterns (31) Big Data (29) Product Architecture (28) MultiThread (27) Soft Skills (27) Concurrency (26) Cracking Code Interview (26) Miscs (25) Distributed (24) OOD Design (24) Google (23) Career (22) Interview - Review (21) Java - Code (21) Operating System (21) Interview Q&A (20) System Design - Practice (20) Tips (19) Algorithm (17) Company - Facebook (17) Security (17) How to Ace Interview (16) Brain Teaser (14) Linux - Shell (14) Redis (14) Testing (14) Tools (14) Code Quality (13) Search (13) Spark (13) Spring (13) Company - LinkedIn (12) How to (12) Interview-Database (12) Interview-Operating System (12) Solr (12) Architecture Principles (11) Resource (10) Amazon (9) Cache (9) Git (9) Interview - MultiThread (9) Scalability (9) Trouble Shooting (9) Web Dev (9) Architecture Model (8) Better Programmer (8) Cassandra (8) Company - Uber (8) Java67 (8) Math (8) OO Design principles (8) SOLID (8) Design (7) Interview Corner (7) JVM (7) Java Basics (7) Kafka (7) Mac (7) Machine Learning (7) NoSQL (7) C++ (6) Chrome (6) File System (6) Highscalability (6) How to Better (6) Network (6) Restful (6) CareerCup (5) Code Review (5) Hash (5) How to Interview (5) JDK Source Code (5) JavaScript (5) Leetcode (5) Must Known (5) Python (5)

Popular Posts