Дашборд управления бизнесом (P&L) в Metabase за 5 дней

                    /*Выбираем нужные таблицы в WITH*/
WITH
    Revenue_bymonth AS (
        SELECT 
            date_trunc('month', "Task_Sum_Date") as rev_MONTH, sum("Plan_Revenue") as Revenue
        FROM magia__income_revenue
        group by rev_MONTH
    ),
…

SELECT
Purchases_bymonth.cost_MONTH,
/*Используем coalesce, чтобы вместо null были 0*/
    coalesce(Revenue_bymonth.Revenue,'0') Revenue, -- Доход
    coalesce(Purchases_bymonth.Purchases,'0') Purchases,  -- Закупки
    coalesce(Salary_bymonth.Salarys,'0') Salarys, -- Зарплаты
    coalesce(Revenue_bymonth.Revenue,'0')-coalesce(Purchases_bymonth.Purchases,'0')-coalesce(Salary_bymonth.Salarys,'0') Profit,    -- Прибыль
    (Revenue_bymonth.Revenue-Purchases_bymonth.Purchases-Salary_bymonth.Salarys)/NULLIF(Revenue_bymonth.Revenue,0) as Profitability -- Рентабельность

FROM purchases_bymonth
LEFT JOIN Revenue_bymonth 
    on Purchases_bymonth.cost_MONTH = Revenue_bymonth.rev_MONTH
LEFT JOIN Salary_bymonth 
    on Purchases_bymonth.cost_MONTH = Salary_bymonth.salary_MONTH

ORDER by Purchases_bymonth.cost_MONTH
                

                    /*Выбираем DIM-таблицы в WITH*/
/*DIM*/
    dim_Revenue_bymonth AS (
        SELECT 
            date_trunc('month', "Task_Sum_Date") as rev_MONTH, sum("Plan_Revenue") as Revenue,
            ds -- Дата снапшота
        FROM magia__income_revenue__dim
        [[where ds = {{ds}}]] -- Выбираем дату снапшота для сравнения. В фигурных скобках — переменные для фильтрации прямо в дашборде Metabase
        group by ds, rev_MONTH
    ),
…
/* Джоиним DIM-таблицы */
/*DIM*/
LEFT JOIN dim_Revenue_bymonth
…
/* Добавляем фильтр по годам */
where Purchases_bymonth.cost_MONTH >= '1.1.{{Year}}' AND Purchases_bymonth.cost_MONTH <= '12.31.{{Year}}'
                

                    /*Добавляем Косвенные затраты в With (которые не относятся ни к одному Цеху)*/
WITH
    /*INDIRECT COSTS*/
    purch AS (
        SELECT
            date_trunc('month', "Cost_Plan_Date") as cost_month, sum("Task_Sum_Cost") as indirect_purchases
        FROM magia__income_expenses
        WHERE "Department" NOT LIKE '%Цех -%'
        GROUP BY cost_month
    ),
    
    Salary AS (
        SELECT
            date_trunc('month', "Month_Date") as salary_month, sum("Total") as indirect_salarys
        FROM magia__profit_salary
        WHERE "Purpose" = 'Непроизводственное' -- Отфильтровываем Непроизводственные
        GROUP BY salary_month
    ),

/*Выбираем все Зарплаты и Закупки по цехам*/
    purch_dev AS (
        SELECT
            date_trunc('month', "Cost_Plan_Date") as cost_month, sum("Task_Sum_Cost") as purch_dev
        FROM magia__income_expenses
        WHERE "Department" = 'Цех - Разработка'
        GROUP BY cost_month
    ),

    purch_projection AS (
        SELECT
            date_trunc('month', "Cost_Plan_Date") as cost_month, sum("Task_Sum_Cost") as purch_projection
        FROM magia__income_expenses
        WHERE "Department" = 'Цех - Проектирование' 
        GROUP BY cost_month
    ),
…
/*Джоиним все таблицы с затратам*/
…
/*Добавляем колонку с рассчитанными Косвенными в P&L и корректируем формулу расчета прибыли в SELECT*/
(coalesce(pb.indirect_purchases,'0') + coalesce(sb.indirect_salarys,'0')) *
        (coalesce(pbd.purch_dev,'0') + coalesce(sdv.indirect_salarys_dev,'0')) / (
            coalesce(pbanalytics.purch_analytics,'0') + coalesce(sa.indirect_salarys_analytics,'0') +
            coalesce(pbprojection.purch_projection,'0') + coalesce(sp.indirect_salarys_projection,'0') + 
            coalesce(pbdesign.purch_design,'0') + coalesce(sd.indirect_salarys_design,'0') + 
            coalesce(pbd.purch_dev,'0') + coalesce(sdv.indirect_salarys_dev,'0') + 
            coalesce(pbm.purch_PM,'0') + coalesce(spm.indirect_salarys_pm,'0')
        ) dev_indirect_costs,