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
    month
  , cp_id
  , sum(kwh) as total_monthly_kwh
  , count(distinct charge_id) as number_of_charges
  , max(completed_at) as latest_charge
from 
    partner.production_examplepartner.charges
where cp_id in (select distinct cp_id from partner.production_examplepartner.charge_points)
group by
    month, cp_id
)
select
    charges_agg.* 
  , cps.* exclude (cp_id)
from
    charges_agg
left join partner.production_examplepartner.charge_points cps
on cps.cp_id = charges_agg.cp_id;

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

with totals as (
    select 
    ch.month as billing_month
  , paying_team_id as team_id
  , ch.user_id
  , vehicle_id
  , sum(kwh) as consumption_kwh
  , sum(to_sub_amount * to_currency_rate_eur) as net_price_eur
  , sum(to_amount * to_currency_rate_eur) as gross_price_eur
  , sum(vat_to_amount * to_currency_rate_eur) as vat_eur
  , sum(price_eur) as gross_price_eur_valid
  , count(distinct charge_id) as number_of_charges
from
    partner.production_examplepartner.charges ch
left join
    partner.production_examplepartner.transactions tr
on
    ch.charge_id = tr.reference_id
    and 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 team_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.user_id = totals.user_id