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

(2 rows)

Solution postgres

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'


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
180 Members moved from Microsoft to Google directly. linkedin hard