SQL Analysis
& Reporting

question_id       text        -- e.g. Q-001
log_date          date        -- meeting date
company_type_raw  text        -- captured free-text at intake (messy)
region            text        -- client HQ / desk region
pillar            text        -- product line, tagged inconsistently
sales_stage       text        -- free-text sales stage at time of capture
question_type     text        -- Functional / Technical / Strategic / Commercial / Compliance
question_text     text
answer_text       text

SELECT
  COUNT(*)                                            AS total_questions,
  COUNT(pillar)                                       AS pillar_tagged,
  COUNT(sales_stage)                                  AS stage_tagged,
  COUNT(question_type)                                AS category_tagged,
  ROUND(COUNT(pillar)       * 100.0 / COUNT(*), 1)    AS pct_pillar,
  ROUND(COUNT(sales_stage)  * 100.0 / COUNT(*), 1)    AS pct_stage,
  ROUND(COUNT(question_type)* 100.0 / COUNT(*), 1)    AS pct_category
FROM voc_questions;

SELECT
  CASE
    WHEN company_type_raw IN ('Tier 2 Regional Bank', 'Tier 2 / Regional Bank')
      THEN 'Tier 2 / Regional Bank'
    WHEN company_type_raw IN ('Vendor / Consulting', 'Consulting / Vendor')
      THEN 'Consulting / Vendor'
    WHEN company_type_raw = 'Asset Management'
      THEN 'Asset Manager / Investment'
    ELSE COALESCE(company_type_raw, '(untagged)')
  END                       AS company_type,
  COUNT(*)                  AS questions
FROM voc_questions
GROUP BY 1
ORDER BY questions DESC;

SELECT
  pillar,
  COUNT(*)                                              AS questions,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1)    AS pct_of_tagged
FROM voc_questions
WHERE pillar IS NOT NULL
GROUP BY pillar
ORDER BY questions DESC;

SELECT
  region,
  COUNT(*)                                    AS questions,
  RANK() OVER (ORDER BY COUNT(*) DESC)        AS rank
FROM voc_questions
WHERE region IS NOT NULL
GROUP BY region
ORDER BY rank;

SELECT
  c.company_type,
  SUM(CASE WHEN q.pillar = 'Collateral Management' THEN 1 ELSE 0 END) AS collateral_mgmt,
  SUM(CASE WHEN q.pillar = 'Structured Products'   THEN 1 ELSE 0 END) AS structured_products,
  SUM(CASE WHEN q.pillar = 'Direct Lending'         THEN 1 ELSE 0 END) AS direct_lending,
  SUM(CASE WHEN q.pillar IS NULL                    THEN 1 ELSE 0 END) AS untagged
FROM voc_questions q
JOIN dim_company_type_clean c ON c.raw_value = q.company_type_raw
WHERE c.company_type IN ('Tier 1 Bank','Asset Manager / Investment','Hedge Fund / Alternative')
GROUP BY c.company_type
ORDER BY untagged DESC;

-- dim_stage_bucket(raw_stage, funnel_stage) maps messy free-text
-- stage values onto four clean funnel buckets
SELECT
  COALESCE(b.funnel_stage, 'Untagged') AS funnel_stage,
  COUNT(*)                             AS questions
FROM voc_questions q
LEFT JOIN dim_stage_bucket b
  ON q.sales_stage = b.raw_stage
GROUP BY 1
ORDER BY questions DESC;

SELECT
  quarter,
  questions,
  SUM(questions) OVER (ORDER BY quarter) AS cumulative_questions
FROM (
  SELECT
    CONCAT(EXTRACT(YEAR FROM log_date), '-Q', EXTRACT(QUARTER FROM log_date)) AS quarter,
    COUNT(*) AS questions
  FROM voc_questions
  WHERE log_date IS NOT NULL
  GROUP BY 1
) quarterly
ORDER BY quarter;