YAML
get_orders_query: &get_orders_query
select:
- field: client_id
- operator: sum
args:
- field: total
filter:
where:
- field: type
operator: =
value: 'sales'
alias: total_sale
- operator: sum
args:
- field: total
filter:
where:
- field: type
operator: =
value: 'refunds'
alias: total_refunds
- operator: avg
args:
- field: total
filter:
where:
- field: type
operator: =
value: sales
alias: avg_ticket
- operator: count
alias: total_orders
args:
- field: "*"
filter:
where:
- field: type
operator: =
value: sales
from: orders
group_by:
- field: client_id
main:
select:
- clients.*
- o.total_sales
- o.total_refunds
- o.avg_ticket
- o.num_of_sales
from: clients
join:
- query:
<<: *get_orders_query
alias: o
type: left_join
on:
- field: o.client_id
operator: =
value: clients.id
SQL
SELECT
clients.*,
o.total_sales,
o.total_refunds,
o.avg_ticket,
o.num_of_sales
FROM
"clients"
LEFT JOIN (
SELECT
client_id,
SUM(total) FILTER(
WHERE
type = sales
) AS total_sale,
SUM(total) FILTER(
WHERE
type = refunds
) AS total_refunds,
AVG(total) FILTER(
WHERE
type = sales
) AS avg_ticket,
COUNT(*) FILTER(
WHERE
type = sales
) AS total_orders
FROM
"orders"
GROUP BY
client_id
) o ON o.client_id = clients.id