Ultimate example

View_1

SELECT    
    a.order_id as order_id,
    a.creation_time,
    a.finalize_time,
    a.c_user_id,
    a.ug_id,
    a.sales_type,
    a.payment_type,
    a.delivery_date,
    a.ag_type_name,
    a.STORE_ID,
    SUM(a.margin) as margin,
    SUM(a.revenue) as revenue,
    LISTAGG(a.arg_id , ', ') WITHIN GROUP (ORDER BY a.order_id) as article_group_list    
FROM
(
SELECT DISTINCT
    O.ID as order_id,
    O.CREATION_DATE as creation_time,
    O.TRANSACTION_DATE as finalize_time,
    --O_ITEMS.ID as oi_id,
    -- SL.AMOUNT as sl_amount,
    U.ID as c_user_id,
    UG.ID as ug_id,
    CASE O.SALE_TYPE_ID
        WHEN 3 THEN 'Интернет магазин'
        WHEN 1 THEN 'Опт'
        WHEN 2 THEN 'Розница'
        WHEN 4 THEN 'Терминал'
        WHEN 5 THEN 'Офис'    
    END as sales_type,
    CASE O.SALE_TYPE_ID
        WHEN 4 THEN 'кредит'
        WHEN 5 THEN 'взаиморасчёты'
        WHEN 1 THEN 'безналичная'
        WHEN 2 THEN 'наличная'
        WHEN 3 THEN 'эквайринг'    
    END as payment_type,
    O.DELIVERY_DATE as delivery_date,
    AGT.NAME as ag_type_name,
    O.STORE_ID,
    -SUM(SL.AMOUNT) as margin,
    -SUM(CASE WHEN SL.AMOUNT > 0 THEN 0 ELSE SL.AMOUNT END) as revenue,
    ARG.ID as arg_id
FROM
    /*
    Trying to analyze sales, i.e. the most simple anomaly detection using the following features
    */
         ULTIMA.D_SALES             o
    JOIN ULTIMA.TP_SALE_ARTICLES    o_items    ON O.ID = O_ITEMS.ORIGINAL_SALE_DOCUMENT_ID    
    JOIN ULTIMA.TD_SALES            sl         ON o.ID = sl.DOCUMENT_ID
    JOIN KERNEL.USERS               u          ON O.CREATOR_ID = U.ID
    JOIN KERNEL.USER_GROUPS         ug         ON UG.ID = U.GROUP_ID
    JOIN ULTIMA.AGENTS              ag         ON AG.ID = O.AGENT_ID
    JOIN ULTIMA.AGENT_TYPES         agt        ON AGT.ID = AG.TYPE_ID
    JOIN ULTIMA.ARTICLES            ar         ON AR.ID = sl.ARTICLE_ID
    JOIN ULTIMA.ARTICLE_GROUPS      arg        ON ARG.ID = AR.GROUP_ID
WHERE 1=1
    --AND o.id = 252261
GROUP BY
    O.ID,
    O.CREATION_DATE,
    O.TRANSACTION_DATE,
    --O_ITEMS.ID,
    -- SL.AMOUNT,
    U.ID,
    UG.ID,    
    --U.NAME,
    --UG.NAME,
    CASE O.SALE_TYPE_ID
        WHEN 3 THEN 'Интернет магазин'
        WHEN 1 THEN 'Опт'
        WHEN 2 THEN 'Розница'
        WHEN 4 THEN 'Терминал'
        WHEN 5 THEN 'Офис'    
    END,
    CASE O.SALE_TYPE_ID
        WHEN 4 THEN 'кредит'
        WHEN 5 THEN 'взаиморасчёты'
        WHEN 1 THEN 'безналичная'
        WHEN 2 THEN 'наличная'
        WHEN 3 THEN 'эквайринг'    
    END,
    O.DELIVERY_DATE,
    AGT.NAME,
    O.STORE_ID,
    arg.id
FETCH FIRST 50000 ROWS ONLY
) a
GROUP BY
    a.order_id,
    a.creation_time,
    a.finalize_time,
    a.c_user_id,
    a.ug_id,
    a.sales_type,
    a.payment_type,
    a.delivery_date,
    a.ag_type_name,
    a.STORE_ID