Skip to content

Instantly share code, notes, and snippets.

@QuantVI

QuantVI/revenue_by_client_advsql2.sql

Created July 23, 2019 23:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save QuantVI/53914eff12dd0ebb284776adda651a2b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save QuantVI/53914eff12dd0ebb284776adda651a2b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. QuantVI created this gist Jul 23, 2019.
    39 changes: 39 additions & 0 deletions revenue_by_client_advsql2.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,39 @@
    WITH
    revenue_by_client
    AS
    (
    SELECT
    f.day,
    c.client_country_name,
    c.client_name,
    SUM(f.revenue * er.rate) revenue_euro
    FROM
    datamart.fact_client_stats_daily f
    JOIN datamart.dim_client c
    ON c.client_id = f.client_id
    JOIN datamart.fact_cpop_exchange_rates_daily er
    ON er.source_currency_id = c.currency_id
    AND er.destination_currency_id = 1
    AND er."day" = f."day"
    GROUP BY
    f.day,
    c.client_country_name,
    c.client_name
    HAVING SUM(f.revenue * er.rate) > 0
    )

    SELECT
    client_country_name,
    client_name,
    --??? ratio
    revenue_euro,
    sum(revenue_euro) over (partition by client_country_name) creveuro,
    sum(revenue_euro) over (partition by day, client_country_name) ratio

    FROM
    revenue_by_client
    WHERE
    day = CURRENT_DATE - 1
    ORDER BY
    ratio DESC
    LIMIT 10