SQLPad
Sign In

179. Members who ever moved from Microsoft to Google

medium linkedin
  • *****************************************************************************************.
  • *********************************************************************************.

Table: employment

Employment history of all LinkedIn members

    Column    |          Type       
--------------+------------------------
 member_id    | bigint       
 company_name | character varying(100)
 start_date   | integer

Sample results 

 member_id
-----------
      8001
      8002
(2 rows)

Solution postgres

SELECT DISTINCT E1.member_id
FROM employment E1
JOIN employment E2
ON E1.member_id = E2.member_id
WHERE E1.start_date < E2.start_date
AND E1.company_name = 'Microsoft'
AND E2.company_name = 'Google'
;

Explanation

This query is looking for members of a company who have worked for both Microsoft and Google, but started at Microsoft before moving on to Google.

  • The SELECT statement is pulling the member_id column from the employment table, which contains information about each member's employment history.
  • The FROM clause specifies the employment table, but it is listed twice with different aliases (E1 and E2) to enable a self-join.
  • The JOIN statement links the two instances of the employment table based on the member_id column, so that the query will only return records where a member has worked at both Microsoft and Google.
  • The WHERE clause filters the results further by only selecting records where the member started at Microsoft first (E1.start_date < E2.start_date) and worked for both Microsoft and Google (E1.company_name = 'Microsoft' AND E2.company_name = 'Google').
  • Finally, the DISTINCT keyword ensures that each member_id is only returned once, even if they have worked for Microsoft and Google multiple times.

Expected results


More Linkedin questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
180 Members moved from Microsoft to Google directly. linkedin hard
21%