#BigQuery #SQL Pipe notation

bq_pipe_notation.sql

-- https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax



from etl.ripley_invoice_lines_insight rili
|>  where project_id = '6733cbe25336c776feafa158'
|>  aggregate sum(net_fcy_rfv) as total_amount
    group by project_id, invoice_id, invoice_action, transaction_currency, functional_currency
-- /*debug*/    |> select *;
|>  EXTEND current_timestamp() as current_timestamp
|>  as inv
|>  left join ( select invoice_id, payment_id, sum(payment_to_line_item_amount) as payment_amount, event_ts, currency, functional_currency
                from etl.ripley_payments_insight
                group by all) as pay
    on inv.invoice_id = pay.invoice_id
|>  left join raw.fx_rates fx
    on true
        and coalesce(inv.transaction_currency, pay.currency) = fx.source_currency
        and coalesce(inv.functional_currency, pay.functional_currency) = fx.target_currency
        and date_trunc(pay.event_ts, day) = fx.valid_from_ts
-- /*debug*/    |> select *;
|>  where payment_id is not null
|>  aggregate sum(total_amount) total_project_amount, sum(payment_amount*conversion_rate) as total_payment_amount
    group by project_id

;