180. Members moved from Microsoft to Google directly.

hard 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
-----------
      8002
(1 row)

Solution postgres

SELECT DISTINCT member_id
FROM (
    SELECT
        member_id,
        company_name,
        LEAD(company_name, 1) OVER (PARTITION BY member_id ORDER BY start_date) AS next_company
    FROM employment
) X
WHERE company_name = 'Microsoft'
AND next_company = 'Google'
;
    

Explanation

This query selects unique member IDs from the employment table where the member worked at Microsoft and their next company was Google.

To achieve this, the query first creates a subquery that selects the member ID, company name, and the next company they worked for (using the LEAD function) for each employment record. The LEAD function is used to look ahead one row within the same member ID group, ordered by start date.

The outer query then filters the subquery results to only include rows where the company name was Microsoft and the next company was Google. The DISTINCT keyword ensures that only unique member IDs are returned.

Expected results


More Linkedin questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
179 Members who ever moved from Microsoft to Google linkedin medium
31%