ピープルデータ分析のためのデータガイド

SELECT
  region,
  COUNT(DISTINCT employee_id)
FROM
  PROD.COMMON.DIM_TEAM_MEMBER
WHERE
  is_current = TRUE AND is_current_team_member = TRUE
GROUP BY
  region

SELECT
  COUNT(DISTINCT employee_id)
FROM
  PROD.COMMON.DIM_TEAM_MEMBER
WHERE
  is_current = TRUE AND is_current_team_member = TRUE

SELECT
  key_talent_status,
  COUNT(*) * 100 / SUM(COUNT(*)) OVER() AS key_talent_percentage
FROM
  PROD.COMMON.DIM_TEAM_MEMBER
WHERE
  is_current = TRUE AND is_current_team_member = TRUE
GROUP BY 1

SELECT
  COUNT(*)
FROM
  PREP.SENSITIVE.DIM_TEAM
WHERE
  is_current = TRUE AND is_team_active = TRUE

SELECT
  SUM(team_members_count)
FROM
  PREP.SENSITIVE.DIM_TEAM
WHERE
  is_current = TRUE AND is_team_active = TRUE

SELECT
  entity,
  COUNT(*)
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  is_current = true
GROUP BY 1

SELECT
  position,
  COUNT(*)
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  is_position_active
    AND is_current
GROUP BY 1

SELECT
  COUNT(*)
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  (job_specialty_single LIKE '%ModelOps%' OR job_specialty_multi LIKE '%ModelOps%')
    AND is_current;

SELECT
  *
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  position LIKE '%Backend Engineer%'
    AND entity = 'GitLab France S.A.S.'
     AND is_current

SELECT
  COUNT(*)
FROM
  PROD.COMMON.fct_team_member_status
WHERE
  exit_impact = 'Yes';

SELECT
  COUNT(*)
FROM
  PROD.COMMON.fct_team_member_status
WHERE
  termination_type = 'Resignation (Voluntary)';

SELECT
  DISTINCT termination_reason
FROM
  PROD.COMMON.fct_team_member_status
WHERE
  termination_type = 'Resignation (Voluntary)'
    AND exit_impact = 'Yes';

SELECT
  *
FROM
  PROD.COMMON.fct_team_status
WHERE
  position LIKE '%Backend Engineer%'
    AND entity = 'GitLab France S.A.S.'
      AND employment_status = 'Active'
        AND is_current

SELECT
  team_id,
  COUNT(*)
FROM
  PROD.COMMON.fct_team_status
WHERE
    employment_status = 'Terminated'
       AND termination_type = 'Resignation (Voluntary)'
           AND is_current
GROUP BY 1;

SELECT
  management_level, COUNT(*)
FROM
  PROD.COMMON.fct_team_status
WHERE
    employment_status = 'Terminated'
       AND termination_type = 'Resignation (Voluntary)'
          AND management_level != 'Individual Contributor'
           AND is_current
GROUP BY 1;

SELECT 
    t.employee_id,
    e.full_name,
    e.position,
    e.team_manager_name,
    t.hire_date,
    DATEDIFF('month', t.hire_date, CURRENT_DATE()) AS tenure_months,
    ROUND(DATEDIFF('year', t.hire_date, CURRENT_DATE()), 1) AS tenure_years
FROM "PROD".common.fct_team_member_history t
JOIN PROD.COMMON_MART_PEOPLE.MART_TEAM_MEMBER_DIRECTORY e 
    ON t.employee_id = e.employee_id AND is_current= TRUE -- Team member's current information
WHERE e.team_manager_name = 'MANAGER_FULL_NAME'           -- Replace with preferred full name of the people manager
  AND t.term_date IS NULL                                 -- Only current team members
ORDER BY tenure_years DESC

WITH job_sequences AS (
    SELECT
        dim_team_member_sk,
        job_title AS current_job,
        LEAD(job_title) OVER(PARTITION BY dim_team_member_sk ORDER BY job_start_date) AS next_job
    FROM "PROD".COMMON.fct_team_member_job_history
)
SELECT
    current_job,
    next_job,
    COUNT(*) AS transition_count
FROM job_sequences
WHERE next_job IS NOT NULL
AND NOT current_job = next_job
GROUP BY current_job, next_job
ORDER BY transition_count DESC;

WITH retention_by_job_family AS (
SELECT
    job_family,
    COUNT(DISTINCT dim_team_member_sk) AS total_team_members,
    COUNT(DISTINCT CASE WHEN is_active_team_member = TRUE THEN dim_team_member_sk END) AS active_team_members,
    ROUND((COUNT(DISTINCT CASE WHEN is_active_team_member = TRUE THEN dim_team_member_sk END) / COUNT(DISTINCT dim_team_member_sk)::FLOAT) * 100) AS retention_rate
FROM "PROD".common.fct_team_member_job_history
GROUP BY job_family
ORDER BY retention_rate DESC
)
SELECT * 
FROM retention_by_job_family
WHERE NOT active_team_members = 0 --Excluded deprecated job families

WITH final AS (
  SELECT
    *,
    DATEDIFF(DAY, absence_start, absence_end) + 1 AS pto_days_requested,
    ROW_NUMBER() OVER (
      PARTITION BY
        employee_id,
        absence_date
      ORDER BY
        absence_end DESC,
        pto_uuid DESC
    )                                             AS pto_rank
  FROM prod.common_mart_people.mart_team_member_absence
  WHERE absence_date <= CURRENT_DATE
    AND pto_days_requested <= 25
    AND COALESCE(pto_group_type, '') != 'EXL'
    AND NOT COALESCE(absence_status, '') IN ('CEO Shadow Program', 'Conference', 'Customer Visit')
  QUALIFY pto_rank = 1
)

SELECT
  QUARTER(absence_date) AS quarter,
  absence_status,
  COUNT(absence_date)   AS absence_count
FROM final
WHERE full_name = 'John Doe'
  AND YEAR(absence_date) = 2024
GROUP BY QUARTER(absence_date), absence_status
ORDER BY quarter ASC, absence_count DESC;

SELECT
    directory.division,
    DATE_TRUNC('month', dates.date_actual) AS month,
    ROUND(AVG(location_factors.location_factor),2) AS average_location_factor
  FROM PROD.COMMON_MART_PEOPLE.MART_TEAM_MEMBER_DIRECTORY AS directory
  INNER JOIN PROD.LEGACY.DATE_DETAILS AS dates
    ON dates.date_actual >= directory.valid_from
        AND dates.date_actual < directory.valid_to
  LEFT JOIN PREP.SENSITIVE.EMPLOYEE_LOCATION_FACTOR_SNAPSHOTS AS location_factors
    ON REPLACE(location_factors.bamboo_employee_number,',','') = directory.employee_id
        AND NOT (directory.valid_to <= location_factors.valid_from
          OR directory.valid_from >= location_factors.valid_to)
  WHERE location_factors.location_factor IS NOT NULL
      AND directory.is_current AND directory.is_current_team_member
  GROUP BY 1, 2

SELECT
  employee_id,
  hire_date,
  DATEDIFF(day, hire_date, CURRENT_DATE())           AS tenure_in_days,
  CASE WHEN tenure_in_days BETWEEN 0 AND 183 THEN '0 - 6 Months'
       WHEN tenure_in_days BETWEEN 184 AND 365 THEN '6 - 12 Months'
       WHEN tenure_in_days BETWEEN 366 AND 1095 THEN '1 - 3 Years'
       WHEN tenure_in_days BETWEEN 1096 AND 2190 THEN '4 - 6 Years'
       WHEN tenure_in_days > 2191 THEN '6+ Years'
       ELSE null
   END                                                  AS tenure_bucketed
FROM PROD.COMMON_MART_PEOPLE.MART_TEAM_MEMBER_DIRECTORY
WHERE is_current AND is_current_team_member