Skip to content

Instantly share code, notes, and snippets.

@mford22392

mford22392/pepsi_investigation.sql

Last active July 12, 2023 23:28
Show Gist options
  • Save mford22392/9cf0fbf53865c11b4308241987837ea4 to your computer and use it in GitHub Desktop.
Save mford22392/9cf0fbf53865c11b4308241987837ea4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
pepsi_investigation.sql
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
AND eligibility:employer_specified_status = 'N'
AND employment:status IN('T', 'Terminated');
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
AND employment:status IN('T', 'Terminated');
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
AND employment:status NOT IN('Active', 'A')
AND employment:tenure:separation_date >= dateadd(day, -90, CURRENT_TIMESTAMP)
AND NOT employment:tenure:separation_date > CURRENT_TIMESTAMP
// earliest: "2023-04-14T00:00:00+00:00"
// latest: "2023-07-10T00:00:00+00:00"
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
AND employment:status NOT IN('Active', 'A')
AND NOT employment:tenure:separation_date > CURRENT_TIMESTAMP;
// earliest: "2020-09-10T00:00:00+00:00"
// latest: "2023-07-10T00:00:00+00:00"
SELECT COUNT(DISTINCT(employee_id)), employment:status FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
AND employment:tenure:separation_date > CURRENT_TIMESTAMP
GROUP BY employment:status;
// earliest: "2023-07-13T00:00:00+00:00"
// latest: "3016-11-30T00:00:00+00:00" (and then "2024-07-27T00:00:00+00:00")
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
AND employment:tenure:separation_date < dateadd(day, -90, CURRENT_TIMESTAMP)
// earliest: "2010-08-21T00:00:00+00:00"
// latest: "2023-04-13T00:00:00+00:00"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment