Project Info

Applovin Query Challenge

Inspiration

As a team, we were interested in learning more about databases, how queries are made, and different methods of storing data. Applovin's challenge seemed like a great opportunity to dip our toes into the world of data through optimizing queries.

What it does

Our scripts utilize data aggregation, caching, and DuckDB to speed up queries on a given dataset.

How we built it

Building off of the starting scripts, we tested and implemented a number of optimizations, learning and testing the viability of different techniques, such as indexing, partitioning, and sorting.

Challenges we ran into

Many of the techniques we wanted to implement weren't feasible, since preprocessing gigabytes of information took a significant amount of time. We were time constrained to around 5-10 minutes of data preprocessing time, and combining techniques like sorting and indexing took too long.

Accomplishments we're proud of

Any queries that fall into our aggregated data tables are extremely fast, and we spent a good amount of time testing which columns to aggregate, coming up with a pretty good set of aggregations (we think).

What we learned

As a team, we gained a lot of experience working together and loved the opportunity to learn about how databases organize data and how architectural decisions influence performance.

What's next

We are excited and eager to dive deeper into learning more about data!

Query Engine Benchmark

Setup

Install dependencies:

pip install -r requirements.txt

Requirements: duckdb>=1.1.1, pandas>=2.2.0

Adding Your Queries

Add your benchmark queries to inputs.py using the provided format:

queries = [
    {
        "select": ["day", {"SUM": "total_price"}],
        "from": "events",
        "where": [{"col": "type", "op": "eq", "val": "click"}],
        "group_by": ["day"],
        "order_by": [{"col": "day", "dir": "asc"}]
    },
    # Add more queries here...
]

Running the Benchmark

Step 1: Prepare Data and Build Rollups

python prepare_and_build.py --data-dir /path/to/data

This will:

Load CSV files into DuckDB
Create optimized rollup tables
Takes ~8-9 minutes on the full dataset (on an Apple M1)

Step 2: Run Queries

python step3_run_queries.py --out-dir /path/to/output --truth-dir /path/to/truth

This will:

Execute all benchmark queries
Output results to CSV files
Compare with ground truth (if provided)
Display performance metrics

Example

# Prepare data (one-time setup)
python prepare_and_build.py --data-dir ./data

# Run queries
python step3_run_queries.py --out-dir ./output --truth-dir ./truth

File Structure

CalHacks12.0/
├── prepare_and_build.py   # Data preparation + rollup building (REQUIRED)
├── step3_run_queries.py   # Query execution (REQUIRED)
├── inputs.py              # Query definitions (ADD YOUR QUERIES HERE)
├── assembler.py           # SQL query builder
├── requirements.txt       # Dependencies
├── step1_prepare_data.py  # Optional: Run data prep separately
├── step2_build_rollups.py # Optional: Run rollup building separately
└── tmp/
    └── baseline.duckdb    # DuckDB database (created on first run)

Optional: Modular Workflow

For development/debugging, you can run steps separately:

# Step 1 only: Prepare data
python step1_prepare_data.py --data-dir ./data

# Step 2 only: Build rollups (after step 1)
python step2_build_rollups.py

# Step 3: Run queries
python step3_run_queries.py --out-dir ./output --truth-dir ./truth

Cleanup

To start fresh:

rm -rf tmp/baseline.duckdb*

Analysis

Compare with all teams

View

Metric

bluething001
4
ArrowDigby
1

Figures cover GitHub contributors during the hackathon window. A co-authored commit counts in full for each author, so per-member totals add up to more than the whole-team figures.

Technology

Found in codeClaimed only

PythonIn code

1 of 1 appear in the indexed code.

AI coding agents

No AI coding agent signals were found in this repository.

Detected from committed agent config files and commit authorship. Absence of a signal is not proof an agent was unused.

Codebase size

Source size

59 KB

Source files

Counts recognized source files only; vendored directories, binaries and lockfiles are excluded, so this is smaller than the repository on disk.