CoffeeShop — Top Products by Revenue

CoffeeShop benchmark · all CoffeeShop queries

Top products by revenue for a given year with JOIN to dim_products

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  dp.subcategory,
  dp.name AS product_name,
  SUM(ol.quantity) AS total_quantity,
  SUM(ol.total_price) AS total_revenue
FROM order_lines AS ol
JOIN dim_products AS dp
  ON ol.product_record_id = dp.record_id
WHERE
  EXTRACT(YEAR FROM ol.order_date) = 2023
GROUP BY
  dp.subcategory,
  dp.name
ORDER BY
  total_revenue DESC
LIMIT 15

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def sa2_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters("SA2")
    year = params.get("year", 2023)
    limit = params.get("limit", 15)

    ol = ctx.get_table("order_lines")
    dp = ctx.get_table("dim_products")
    col = ctx.col
    lit = ctx.lit

    return (
        ol.with_columns(col("order_date").dt.year().alias("year"))
        .filter(col("year") == lit(year))
        .join(dp, left_on="product_record_id", right_on="record_id")
        .group_by("subcategory", "name")
        .agg(
            col("quantity").sum().alias("total_quantity"),
            col("total_price").sum().alias("total_revenue"),
        )
        .rename({"name": "product_name"})
        .sort("total_revenue", descending=True)
        .limit(limit)
    )

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import CoffeeShop

bench = CoffeeShop(scale_factor=0.001)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['SA2'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('coffeeshop', 'SA2', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/coffeeshop/queries.py