-- chart summary
WITH
base_data AS (
SELECT
"month",
code,
project_id,
leading_studio,
supporting_studio,
project_manager_id,
project_member_id,
bu_id,
status,
pipeline_revenue,
billing_revenue,
received_cashflow,
billing_expense
FROM
-- "Data Platform"."0, QC environment"."project_revenue_expenses"
{DREMIO_ADDRESS_FOLDER1}."project_revenue_expenses"
WHERE
"bu_key" = 'BRAND_SOLUTIONS'
),
project_status_sequence AS (
SELECT
code,
"month",
status,
ROW_NUMBER() OVER (
PARTITION BY code
ORDER BY "month" DESC
) AS rn
FROM
base_data
),
last_non_completed_status AS (
SELECT
code,
MAX("month") FILTER (
WHERE status NOT IN ('completed', 'cancelled')
) AS last_active_month
FROM
base_data
GROUP BY
code
),
filtered_projects AS (
SELECT
b."month",
b.code,
b.project_id,
b.leading_studio,
b.supporting_studio,
b.project_manager_id,
b.project_member_id,
b.bu_id,
b.status,
b.pipeline_revenue,
b.billing_revenue,
b.received_cashflow,
b.billing_expense
FROM
base_data b
LEFT JOIN
last_non_completed_status l
ON b.code = l.code
WHERE
-- Giữ lại các dự án CHƯA TỪNG completed/cancelled
l.code IS NOT NULL
-- Hoặc đã completed/cancelled nhưng CÓ hoạt động sau đó
OR (
b.status IN ('completed', 'cancelled')
AND b."month" >= '{from_date}'
)
),
latest_rows AS (
SELECT
"month",
code,
project_id,
leading_studio,
supporting_studio
project_manager_id,
project_member_id,
bu_id,
status,
pipeline_revenue,
billing_revenue,
received_cashflow,
billing_expense,
ROW_NUMBER() OVER (PARTITION BY code ORDER BY "month" DESC) AS row_num
FROM
filtered_projects
WHERE
"month" >= '{from_date}' AND "month" <= '{to_date}'
-- "month" >= '202302' AND "month" <= '202302'
),
current_metrics AS (
SELECT
SUM(pipeline_revenue) AS pipeline_revenue,
SUM(billing_revenue) AS billing_revenue,
SUM(received_cashflow) AS received_cashflow,
SUM(billing_revenue) - SUM(billing_expense) AS billing_cm1
FROM
latest_rows
WHERE
1=1
AND (
CASE
WHEN '{studio}' = 'All Studio' THEN leading_studio
ELSE '{studio}'
END = leading_studio
)
AND (
CASE
WHEN '{project_status}' = 'All Status' THEN status
ELSE '{project_status}'
END = status
)
-- Truyền biến phân quyền
-- AND a.project_manager_id = 'fd' -- truyền biến
-- AND a.project_member_id = 'fd' -- truyền biến
-- AND a.bu_id = 6 -- truyền biến
),
filtered_projects_lp AS (
SELECT
b."month",
b.code,
b.project_id,
b.leading_studio,
b.supporting_studio,
b.project_manager_id,
b.project_member_id,
b.bu_id,
b.status,
b.pipeline_revenue,
b.billing_revenue,
b.received_cashflow,
b.billing_expense
FROM
base_data b
LEFT JOIN
last_non_completed_status l
ON b.code = l.code
WHERE
-- Giữ lại các dự án CHƯA TỪNG completed/cancelled
l.code IS NOT NULL
-- Hoặc đã completed/cancelled nhưng CÓ hoạt động sau đó
OR (
b.status IN ('completed', 'cancelled')
AND b."month" >= '{lp_from_date}'
)
),
latest_rows_lp AS (
SELECT
"month",
code,
project_id,
leading_studio,
supporting_studio,
project_manager_id,
project_member_id,
bu_id,
status,
pipeline_revenue,
billing_revenue,
received_cashflow,
billing_expense
FROM
filtered_projects_lp
WHERE
-- "month" >= '202301' AND "month" <= '202301'
"month" >= '{lp_from_date}' AND "month" <= '{lp_to_date}'
),
lp_metrics AS (
SELECT
SUM(pipeline_revenue) AS pipeline_revenue,
SUM(billing_revenue) AS billing_revenue,
SUM(received_cashflow) AS received_cashflow,
SUM(billing_revenue) - SUM(billing_expense) AS billing_cm1
FROM
latest_rows_lp
WHERE
1=1
AND (
CASE
WHEN '{studio}' = 'All Studio' THEN leading_studio
ELSE '{studio}'
END = leading_studio
)
AND (
CASE
WHEN '{project_status}' = 'All Status' THEN status
ELSE '{project_status}'
END = status
)
-- Truyền biến phân quyền
-- AND a.project_manager_id = 'fd' -- truyền biến
-- AND a.project_member_id = 'fd' -- truyền biến
-- AND a.bu_id = 6 -- truyền biến
),
filtered_projects_cply AS (
SELECT
b."month",
b.code,
b.project_id,
b.leading_studio,
b.supporting_studio,
b.project_manager_id,
b.project_member_id,
b.bu_id,
b.status,
b.pipeline_revenue,
b.billing_revenue,
b.received_cashflow,
b.billing_expense
FROM
base_data b
LEFT JOIN
last_non_completed_status l
ON b.code = l.code
WHERE
-- Giữ lại các dự án CHƯA TỪNG completed/cancelled
l.code IS NOT NULL
-- Hoặc đã completed/cancelled nhưng CÓ hoạt động sau đó
OR (
b.status IN ('completed', 'cancelled')
AND b."month" >= '{cply_from_date}'
)
),
latest_rows_cply AS (
SELECT
"month",
code,
project_id,
leading_studio,
supporting_studio,
project_manager_id,
project_member_id,
bu_id,
status,
pipeline_revenue,
billing_revenue,
received_cashflow,
billing_expense
FROM
filtered_projects_cply
WHERE
-- "month" >= '202202' AND "month" <= '202202'
"month" >= '{cply_from_date}' AND "month" <= '{cply_to_date}'
),
cply_metrics AS (
SELECT
SUM(pipeline_revenue) AS pipeline_revenue,
SUM(billing_revenue) AS billing_revenue,
SUM(received_cashflow) AS received_cashflow,
SUM(billing_revenue) - SUM(billing_expense) AS billing_cm1
FROM
latest_rows_cply
WHERE
1=1
AND (
CASE
WHEN '{studio}' = 'All Studio' THEN leading_studio
ELSE '{studio}'
END = leading_studio
)
AND (
CASE
WHEN '{project_status}' = 'All Status' THEN status
ELSE '{project_status}'
END = status
)
-- Truyền biến phân quyền
-- AND a.project_manager_id = 'fd' -- truyền biến
-- AND a.project_member_id = 'fd' -- truyền biến
-- AND a.bu_id = 6 -- truyền biến
)
SELECT
m.pipeline_revenue,
m.billing_revenue,
CASE WHEN p.billing_revenue IS NULL OR p.billing_revenue = 0 THEN NULL
ELSE ROUND((m.billing_revenue - p.billing_revenue) * 100.0 / p.billing_revenue, 1)
END AS billing_revenue_lp,
CASE WHEN c.billing_revenue IS NULL OR c.billing_revenue = 0 THEN NULL
ELSE ROUND((m.billing_revenue - c.billing_revenue) * 100.0 / c.billing_revenue, 1)
END AS billing_revenue_cply,
m.received_cashflow,
CASE WHEN p.received_cashflow IS NULL OR p.received_cashflow = 0 THEN NULL
ELSE ROUND((m.received_cashflow - p.received_cashflow) * 100.0 / p.received_cashflow, 1)
END AS received_cashflow_lp,
CASE WHEN c.received_cashflow IS NULL OR c.received_cashflow = 0 THEN NULL
ELSE ROUND((m.received_cashflow - c.received_cashflow) * 100.0 / c.received_cashflow, 1)
END AS received_cashflow_cply,
m.billing_cm1,
CASE WHEN p.billing_cm1 IS NULL OR p.billing_cm1 = 0 THEN NULL
ELSE ROUND((m.billing_cm1 - p.billing_cm1) * 100.0 / p.billing_cm1, 1)
END AS billing_cm1_lp,
CASE WHEN c.billing_cm1 IS NULL OR c.billing_cm1 = 0 THEN NULL
ELSE ROUND((m.billing_cm1 - c.billing_cm1) * 100.0 / c.billing_cm1, 1)
END AS billing_cm1_cply
FROM
current_metrics m
CROSS JOIN
lp_metrics p
CROSS JOIN
cply_metrics c
Telegram: DB developers channel