
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'analytics'
  AND table_name   = 'dim_employee'
ORDER BY ordinal_position;



-- 22_step7_software_cost_per_employee_allocation_2025.sql
-- Goal:
-- 1) Company software spend (2025) / total employees (2025) => software_cost_per_employee_2025
-- 2) Allocate to each team by headcount: team_headcount * software_cost_per_employee_2025

BEGIN;

CREATE SCHEMA IF NOT EXISTS analytics;

-- Safety: if you keep changing column names, drop views first to avoid rename conflicts
DROP VIEW IF EXISTS analytics.v_software_cost_per_employee_company_2025;
DROP VIEW IF EXISTS analytics.v_employee_count_by_team_2025;
DROP VIEW IF EXISTS analytics.v_software_cost_allocated_by_team_2025;

-- 1) Company-wide software cost per employee (2025)
CREATE VIEW analytics.v_software_cost_per_employee_company_2025 AS
WITH employee_count AS (
  SELECT COUNT(*)::int AS total_employees_2025
  FROM analytics.dim_employee
  WHERE (start_date IS NULL OR start_date <= '2025-12-31'::date)
    AND (end_date   IS NULL OR end_date   >= '2025-01-01'::date)
),
company_spend AS (
  SELECT SUM(COALESCE(total_spend_2025, 0))::numeric AS total_software_spend_2025
  FROM analytics.vendors_2025_clean
)
SELECT
  c.total_software_spend_2025,
  e.total_employees_2025,
  ROUND(c.total_software_spend_2025 / NULLIF(e.total_employees_2025, 0), 2) AS software_cost_per_employee_2025
FROM company_spend c
CROSS JOIN employee_count e;

-- 2) Headcount by team (2025)
CREATE VIEW analytics.v_employee_count_by_team_2025 AS
SELECT
  COALESCE(NULLIF(TRIM(team), ''), 'Unknown') AS team,
  COUNT(*)::int AS team_employees_2025
FROM analytics.dim_employee
WHERE (start_date IS NULL OR start_date <= '2025-12-31'::date)
  AND (end_date   IS NULL OR end_date   >= '2025-01-01'::date)
GROUP BY 1
ORDER BY 2 DESC, 1;

-- 3) Allocate software cost to team = per-employee * team headcount
CREATE VIEW analytics.v_software_cost_allocated_by_team_2025 AS
SELECT
  t.team,
  t.team_employees_2025,
  c.software_cost_per_employee_2025,
  ROUND(t.team_employees_2025 * c.software_cost_per_employee_2025, 2) AS allocated_software_cost_2025
FROM analytics.v_employee_count_by_team_2025 t
CROSS JOIN analytics.v_software_cost_per_employee_company_2025 c
ORDER BY allocated_software_cost_2025 DESC, t.team;

COMMIT;

-- Quick checks (run whenever)
SELECT * FROM analytics.v_software_cost_per_employee_company_2025;
SELECT * FROM analytics.v_software_cost_allocated_by_team_2025;





-- 1) Sanity check: how many total rows in dim_employee?
SELECT COUNT(*) AS total_rows
FROM analytics.dim_employee;

-- 2) Headcount by employment_type (this will answer your question fast)
SELECT
  COALESCE(NULLIF(TRIM(employment_type), ''), 'Unknown') AS employment_type,
  COUNT(*) AS employees
FROM analytics.dim_employee
GROUP BY 1
ORDER BY employees DESC;

-- 3) Who is being counted as "active in 2025" (same logic we used)
SELECT
  COALESCE(NULLIF(TRIM(employment_type), ''), 'Unknown') AS employment_type,
  COUNT(*) AS employees_active_2025
FROM analytics.dim_employee
WHERE (start_date IS NULL OR start_date <= '2025-12-31'::date)
  AND (end_date   IS NULL OR end_date   >= '2025-01-01'::date)
GROUP BY 1
ORDER BY employees_active_2025 DESC;

-- 4) Quick list of the distinct values (in case data is messy like "Contractor " vs "contract")
SELECT DISTINCT employment_type
FROM analytics.dim_employee
ORDER BY 1;


---
SELECT
    total_software_spend_2025,
    59 AS projected_employees_2026,
    ROUND(total_software_spend_2025 / 59, 2) AS software_cost_per_employee_2026
FROM analytics.v_software_cost_per_employee_company_2025;



------

SELECT
    team AS department,
    COUNT(*) AS employee_count
FROM analytics.budget_master_clean
GROUP BY team

UNION ALL

SELECT
    'TOTAL' AS department,
    COUNT(*) AS employee_count
FROM analytics.budget_master_clean
ORDER BY department;


------
SELECT
    year,
    COUNT(*) AS employee_count
FROM (
    SELECT
        employee_id,
        2025 AS year
    FROM analytics.budget_master_clean
    WHERE start_date <= '2025-12-31'
      AND (end_date IS NULL OR end_date >= '2025-01-01')

    UNION ALL

    SELECT
        employee_id,
        2026 AS year
    FROM analytics.budget_master_clean
    WHERE start_date <= '2026-12-31'
      AND (end_date IS NULL OR end_date >= '2026-01-01')
) t
GROUP BY year
ORDER BY year;


------

WITH years AS (
    SELECT 2025 AS year
    UNION ALL
    SELECT 2026
)

SELECT
    y.year,
    COUNT(e.employee_id) AS employee_count
FROM years y
JOIN analytics.budget_master_clean e
  ON e.start_date <= make_date(y.year,12,31)
 AND (e.end_date IS NULL OR e.end_date >= make_date(y.year,1,1))
GROUP BY y.year
ORDER BY y.year;



SELECT
    year,
    total_software_spend,
    employees,
    ROUND(total_software_spend / employees, 2) AS software_cost_per_employee
FROM (
    
    SELECT
        2025 AS year,
        SUM(avg_monthly_spend_2025) * 12 AS total_software_spend,
        50 AS employees
    FROM analytics.vendors_2025_clean

    UNION ALL

    SELECT
        2026 AS year,
        SUM(avg_monthly_spend_2025) * 12 AS total_software_spend,
        59 AS employees
    FROM analytics.vendors_2025_clean

) t
ORDER BY year;