Example Queries

The queries below are examples of gaining insights using the data available on the Partner Data Warehouse. To reproduce these, replace examplepartnerwith the partnername we provide.

Count number of charges and KWH delivered on each Charge Point over months

with charges_agg as (
select
    date_trunc('month', completed_at) as month
  , charge_point_id
  , sum(watt_hours / 1000.0)          as total_monthly_kwh
  , count(distinct id)                as number_of_charges
  , max(completed_at)                 as latest_charge
from
    partner.production_examplepartner.charges
where charge_point_id in (select distinct id from partner.production_examplepartner.charge_points)
group by
    month, charge_point_id
)
select
    charges_agg.*
  , cps.* exclude (id)
from
    charges_agg
left join partner.production_examplepartner.charge_points cps
    on cps.id = charges_agg.charge_point_id;

Calculate the amount of money each user has spent in a given team with a given vehicle, for each month.

Transaction amounts are returned in the transaction's to_currency_code — no EUR normalisation is applied as there is no EUR exchange rate column in the transactions table.

with totals as (
    select
        date_trunc('month', ch.completed_at) as billing_month
      , paying_team_id                        as team_id
      , ch.user_id
      , vehicle_id
      , sum(ch.watt_hours / 1000.0)           as consumption_kwh
      , sum(tr.to_sub_amount)                 as net_price
      , sum(tr.to_amount)                     as gross_price
      , sum(tr.vat_to_amount)                 as vat
      , count(distinct ch.id)                 as number_of_charges
    from
        partner.production_examplepartner.charges ch
    left join
        partner.production_examplepartner.transactions tr
        on  ch.id                     = tr.reference_id
        and tr.reference_type         = 'CHARGE'
        and ch.paying_team_id         = tr.from_wallet_owner_id
        and tr.from_wallet_owner_type = 'TEAM'
        and tr.state                  = 'complete'
    where
        ch.paying_team_id in (select distinct id from partner.production_examplepartner.teams)
        and ch.completed_at is not null
    group by
        billing_month
      , paying_team_id
      , ch.user_id
      , vehicle_id
)
select
    totals.*
  , users.first_name
  , users.last_name
  , users.email
from totals
left join partner.production_examplepartner.users users
    on users.id = totals.user_id