Chartio Users Get 50% Off bipp's Premium Plan for 12 months

Dynamic Views with Structured SQL

Your reports and visualizations have gone over so well, the sales manager wants more! How about the date with the second-highest sales? Oh, and present it for all countries or better yet, let me dynamically specify the country as I'm viewing your visualization! Not to worry, bipp has you covered!

In the SQL, use three levels of nesting of SELECT statements. The country filter is applied at the innermost level. The daily_sales table is marked as a reftable rather than table in the retail dataset. This prevents the table from appearing in the data viewer’s list of tables.

dynamic views bipp

dynamic views bipp

dynamic views bipp

Select the columns:

dynamic views columns

Set the filters using the Sale Rank from Daily Ranked Sales and Country from Transactions:

dynamic views filters

bipp generates the SQL:

SELECT t000.invoice_num AS _0, t000.invoice_date AS _1,
    t000.sale_amount AS _2
FROM (
    SELECT t000.invoice_num AS invoice_num,
    t000.invoice_date AS invoice_date,
    sum(t000.amount) AS sale_amount,
    RANK() OVER(ORDER BY (sum(t000.amount)) desc
        ) AS sale_rank
    FROM (
        SELECT t000.invoice_num AS invoice_num,
            cast(t000.invoice_date as DATE) AS invoice_date,
            sum(t000.invoice_qnty * t000.unit_price)
                AS amount
        FROM `online_retail`.`transactions` AS t000
        WHERE (t000.country = 'Germany')
        GROUP BY 1, 2
    ) AS t000
    GROUP BY 1, 2
) AS t000
WHERE (t000.sale_rank = 2)
GROUP BY 1, 2, 3

Fetch the data:

dynamic views results

IN THIS PAGE