AMPLab Q2 — Join Query

AMPLab benchmark · all AMPLab queries

Join uservisits to rankings with date range, GROUP BY sourceIP

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  sourceIP,
  SUM(adRevenue) AS totalRevenue,
  AVG(pageRank) AS avgPageRank
FROM uservisits AS uv
JOIN rankings AS r
  ON uv.destURL = r.pageURL
WHERE
  uv.visitDate BETWEEN '2000-01-01' AND '2000-01-03'
GROUP BY
  sourceIP
ORDER BY
  totalRevenue DESC
LIMIT 100

Representative DataFrame

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

def q2_expression_impl(ctx: DataFrameContext) -> Any:
    params = get_parameters("Q2")
    start_date = params.get("start_date", date(2000, 1, 1))
    end_date = params.get("end_date", date(2000, 1, 3))
    limit_rows = params.get("limit_rows", 100)

    uservisits = ctx.get_table("uservisits")
    rankings = ctx.get_table("rankings")
    col = ctx.col
    lit = ctx.lit

    visit_date = col("visitDate").cast_date()
    return (
        uservisits.filter((visit_date >= lit(start_date)) & (visit_date <= lit(end_date)))
        .join(rankings, left_on="destURL", right_on="pageURL")
        .group_by("sourceIP")
        .agg(
            col("adRevenue").sum().alias("totalRevenue"),
            col("pageRank").mean().alias("avgPageRank"),
        )
        .sort("totalRevenue", descending=True)
        .limit(limit_rows)
    )

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 AMPLab

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

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('amplab', '2', family="expression").source)  # or family="pandas"

Source

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