Estudo de Caso Analytics Engineer

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

SELECT
  a.*,
  -- Aqui eu calculo a soma cumulativa do total de depósitos de cada cliente ordenando
  -- por mês, se for nulo, altero o valor para 0, então subtraio a soma cumulativa do
  -- total de retiradas para cada cliente ordenando por mês, se for nulo, altero o
  -- valor para 0 e eu salvo isso como account_monthly_balance
  NVL(SUM(total_transfer_in) OVER (PARTITION BY customer_id 
ORDER BY
  action_month), 0) - NVL(SUM(total_transfer_out) OVER (PARTITION BY customer_id 
ORDER BY
  action_month), 0) AS account_monthly_balance 
FROM
  -- subconsulta total de transações de entrada/saída
( 
  SELECT
    * 
  FROM
    -- subconsulta de depósitos totais
( 
    SELECT
      action_month, customer_id, SUM(amount) AS total_transfer_in 
    FROM
      (
        SELECT
          * 
        FROM
          -- subconsulta de depósitos regulares
( 
          SELECT
            d_month.action_month, accounts.customer_id, SUM(transfer_ins.amount) AS amount 
          FROM
            d_time 
            INNER JOIN
              transfer_ins 
              ON transfer_ins.transaction_completed_at = d_time.time_id 
            LEFT JOIN
              d_month USING(month_id) 
            LEFT JOIN
              accounts USING(account_id) 
          WHERE
            transfer_ins.status = 'completed' 
          GROUP BY
            d_month.action_month, accounts.customer_id ) transfer_in 
          UNION ALL
          SELECT
            * 
          FROM
            -- subconsulta de depósitos pix
( 
            SELECT
              d_month.action_month, accounts.customer_id, SUM(pix_movements.pix_amount) AS amount 
            FROM
              d_time 
              INNER JOIN
                pix_movements 
                ON pix_movements.pix_completed_at = d_time.time_id 
              LEFT JOIN
                d_month USING(month_id) 
              LEFT JOIN
                accounts USING(account_id) 
            WHERE
              pix_movements.status = 'completed' 
              AND pix_movements.in_or_out = 'pix_in' 
            GROUP BY
              d_month.action_month, accounts.customer_id ) 
      )
    GROUP BY
      action_month, customer_id ) 
      FULL JOIN
        (
          SELECT
            action_month,
            customer_id,
            SUM(amount) AS total_transfer_out 
          FROM
            -- subconsulta de saques totais
( 
            SELECT
              * 
            FROM
              -- subconsulta de retirada regular
( 
              SELECT
                d_month.action_month, accounts.customer_id, SUM(transfer_outs.amount) AS amount 
              FROM
                d_time 
                INNER JOIN
                  transfer_outs 
                  ON transfer_outs.transaction_completed_at = d_time.time_id 
                LEFT JOIN
                  d_month USING(month_id) 
                LEFT JOIN
                  accounts USING(account_id) 
              WHERE
                transfer_outs.status = 'completed' 
              GROUP BY
                d_month.action_month, accounts.customer_id ) 
              UNION ALL
              SELECT
                * 
              FROM
                -- subconsulta de retirada de pix
( 
                SELECT
                  d_month.action_month, accounts.customer_id, SUM(pix_movements.pix_amount) AS amount 
                FROM
                  d_time 
                  INNER JOIN
                    pix_movements 
                    ON pix_movements.pix_completed_at = d_time.time_id 
                  LEFT JOIN
                    d_month USING(month_id) 
                  LEFT JOIN
                    accounts USING(account_id) 
                WHERE
                  pix_movements.status = 'completed' 
                  AND pix_movements.in_or_out = 'pix_out' 
                GROUP BY
                  d_month.action_month, accounts.customer_id ) ) 
                GROUP BY
                  action_month,
                  customer_id 
        )
        USING (action_month, customer_id) ) a;