Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
99 lines (91 sloc) 4.02 KB
************************************************************************
COMPANIES
************************************************************************
CREATE TABLE companies (
member_id int,
company_name varchar(80),
year_start int
);
INSERT INTO companies VALUES (1,'Google', 1990);
INSERT INTO companies VALUES (1,'Microsoft', 2000);
INSERT INTO companies VALUES (2,'Microsoft', 2000);
INSERT INTO companies VALUES (2,'Google', 2001);
INSERT INTO companies VALUES (3,'Microsoft', 1997);
INSERT INTO companies VALUES (3,'Google', 1998);
INSERT INTO companies VALUES (4,'Microsoft', 1997);
INSERT INTO companies VALUES (4,'LinkedIn', 1998);
INSERT INTO companies VALUES (4,'Google', 2000);
+-----------+--------------+------------+
| member_id | company_name | year_start |
+-----------+--------------+------------+
| 1 | Google | 1990 |
| 1 | Microsoft | 2000 |
| 2 | Microsoft | 2000 |
| 2 | Google | 2001 |
| 3 | Microsoft | 1997 |
| 3 | Google | 1998 |
| 4 | Microsoft | 1997 |
| 4 | LinkedIn | 1998 |
| 4 | Google | 2000 |
+-----------+--------------+------------+
1) COUNT MEMBERS WHO EVER MOVED FROM MICROSOFT TO GOOGLE
step 1
select distinct(a.member_id)
from (select member_id,company_name,
dense_rank() over (partition by member_id order by year_start asc) as rnk
from companies ) a
join
(select member_id,company_name, dense_rank() over (partition by member_id order by year_start asc) as rnk
from companies) b
on a.member_id=b.member_id
where a.company_name='microsoft' and b.company_name='google'
+-----------+-----+--------------+-----------+-----+--------------+
| member_id | rnk | company_name | member_id | rnk | company_name |
+-----------+-----+--------------+-----------+-----+--------------+
| 1 | 2 | Microsoft | 1 | 1 | Google |
| 2 | 1 | Microsoft | 2 | 2 | Google |
| 3 | 1 | Microsoft | 3 | 2 | Google |
| 4 | 1 | Microsoft | 4 | 3 | Google |
+-----------+-----+--------------+-----------+-----+--------------+
step2 : specify the rank
select distinct(a.member_id)
from (select member_id,company_name,
dense_rank() over (partition by member_id order by year_start asc) as rnk
from companies ) a
join
(select member_id,company_name, dense_rank() over (partition by member_id order by year_start asc) as rnk
from companies) b
on a.member_id=b.member_id
where a.company_name='microsoft' and b.company_name='google'
and a.rnk=1 and b.rnk >=2;
+-----------+
| member_id |
+-----------+
| 2 |
| 3 |
| 4 |
+-----------+
select a.member_id,a.company_name,b.company_name
from companies a, companies b
where a.member_id=b.member_id
and a.year_start < b.year_start
and a.company_name='microsoft'
and b.company_name='google';
+-----------+--------------+--------------+
| member_id | company_name | company_name |
+-----------+--------------+--------------+
| 2 | Microsoft | Google |
| 3 | Microsoft | Google |
| 4 | Microsoft | Google |
+-----------+--------------+--------------+
2)) COUNT MEMBERS WHO DIRECTLY MOVED FROM MICROSOFT TO GOOGLE? (MICRSOFT --LINKEDIN -- GOOGLE DOESN'T COUNT)
select distinct(a.member_id)
from (select member_id,company_name,
dense_rank() over (partition by member_id order by year_start asc) as rnk
from companies ) a
join
(select member_id,company_name, dense_rank() over (partition by member_id order by year_start asc) as rnk
from companies) b
on a.member_id=b.member_id
where a.company_name='microsoft' and b.company_name='google'
and a.rnk=1 and b.rnk =2;