perfetto-sql

What this skill does

## Guidelines and Hints

- **Idempotency:** Ensure queries are idempotent to prevent "already exists" errors during multiple executions.
  - For Perfetto objects, always use `CREATE OR REPLACE`: `CREATE OR REPLACE
    PERFETTO TABLE`, `CREATE OR REPLACE PERFETTO VIEW`, `CREATE OR REPLACE
    PERFETTO FUNCTION`, `CREATE OR REPLACE PERFETTO MACRO`.
  - For SQLite Virtual Tables (such as `SPAN_JOIN`), `CREATE OR REPLACE` is not supported. Explicitly drop them first: `DROP TABLE IF EXISTS
    my_table; CREATE VIRTUAL TABLE my_table USING SPAN_JOIN(...);`
  - For standard SQLite indexes, prepend `DROP INDEX IF EXISTS index_name;`.
- `SPAN_JOIN` will crash if intervals **within the same input table** overlap. Always use the `PARTITIONED {column}` (for example, `PARTITIONED upid`) clause to isolate intervals.
- Intermediate tables fed into a `SPAN_JOIN` must be materialized using `CREATE PERFETTO TABLE`, not `CREATE VIEW`.
- **Trace Boundaries (`dur = -1`):** Slices or thread states that don't finish before the trace ends are recorded with `dur = -1`. When calculating a bounding box (for example, `ts + dur`) or summing durations (`SUM(dur)`), handle incomplete durations using: `IIF(dur = -1, trace_end() - ts, dur)`.
- **Robust State Transitions:** Avoid manual timestamp arithmetic (for example, `ts + dur = next.ts`) to join adjacent events. Rely on standard library modules (for example, `sched.runnable`, `linux.perf.counters`, `intervals.overlap`) which safely handle trace gaps and preemptions.
- **Unique Identifiers:** When writing SQL queries in Perfetto, you must join tables using `utid` (unique thread ID) or `upid` (unique process ID) instead of the regular `tid` or `pid`. **Why it's useful** : The operating system recycles `TIDs` and `PIDs`, while `UTIDs` and `UPIDs` remain unique for the lifetime of the trace, which prevents incorrect joins.
- **Safe Argument Extraction:** Use `EXTRACT_ARG(arg_set_id, 'key')` to extract dictionary or JSON-like properties from slices or tracks. Don't attempt string parsing.
- **String Matching (Always use GLOB):** Use `GLOB` instead of `LIKE`. `LIKE` causes performance bottlenecks and treats underscores (`_`) as wildcards, leading to bugs.
  - **Exact matches:** Use `=`.
  - **Substring matches:** Use `GLOB` with `*` (for example, `name GLOB
    '*RenderThread*'`).
  - **Case-insensitive matches:** Use `LOWER(name) GLOB` and make sure the search string is fully lowercase (for example, `LOWER(name) GLOB
    '*renderthread*'`). Use this when dealing with inconsistent trace capitalization (for example, `WakeLock` versus `wakelock`).
- **Calculating Time Overlaps:** To calculate the overlap duration between two
  time intervals `[start1, end1]` and `[start2, end2]`:

  > **Precedence Rule:** Always prefer using `SPAN_JOIN` or standard library
  > functions (for example, `intervals.overlap`) to calculate overlaps
  > **between two different sets of intervals** . Avoid manual arithmetic if a
  > standard library feature or `SPAN_JOIN` can achieve the same result. Use
  > the following logic if no built-in alternative exists.
  1. **Condition:** The intervals overlap if `start1 < end2` and `start2 <
     end1`.
  2. **Duration:** The overlap duration is calculated as `MIN(end1, end2) -
     MAX(start1, start2)`

     > **Important:** Incomplete Perfetto slices have a duration of -1
     > (`dur = -1`). Always calculate the effective end time using `ts +
     > IIF(dur = -1, trace_end() - ts, dur)` before applying this logic.
- Query `android_thread_slices_for_all_startups` for app startup requests.

- Join `counter_track` with `counter` to get values of counter with a specific
  name.

- When querying for a CPU frequency counter, include the `linux.cpu.frequency`
  module and use the `cpu_frequency_counters` table.

- When looking for events around a specific timestamp, start with 100ms as the
  window size.

- Always prefix column names with table or view alias, that is:
  `{alias}.{column_name}`.

- To calculate the total time spent in slices matching a specific name pattern
  (for example, `*{name_pattern}*`), you must sum their durations. **Why it's
  useful** : This helps quantify the total impact of a specific function or
  feature on performance across multiple calls. Here is an example query (note
  the safe handling of incomplete slices): `sql SELECT count(*) as
  total_count, sum(IIF(slice.dur = -1, trace_end() - slice.ts, slice.dur)) /
  1000000.0 as total_dur_ms FROM slice WHERE slice.name GLOB
  '*{name_pattern}*';`

## Resources

- **Documentation:** The Perfetto Standard Library documentation is in [`perfetto-stdlib.md`](references/perfetto-stdlib.md). Use this file as a reference to discover available modules, find schemas (columns and types) for specific tables or views, or determine the `INCLUDE PERFETTO MODULE` statements required before drafting SQL query.
- **Execution Tool:** Queries are executed using the official `trace_processor` wrapper script downloaded directly from Perfetto. Output is returned in pure CSV format.

## Execution Protocol

You must follow these steps sequentially, mirroring a multi-agent pipeline:

### Step 0: Tool Setup

**Fetch the Wrapper:** You must use the top level of the current project workspace (`./trace_processor`).

> **CRITICAL GUARDRAIL:** NEVER use filesystem search tools (`find`, `find_by_name`, `grep`, `dir /s`, `Get-ChildItem`) across the home directory or workspace to locate `trace_processor` — unconstrained searches across entire workspaces will stop responding or time out.

Perform a direct file check at the top level of your workspace (e.g., `ls trace_processor`). If missing, download `https://get.perfetto.dev/trace_processor` directly into the root workspace (`curl -LO`), make it executable on macOS/Linux (`chmod +x`), and ensure `trace_processor` is added to `.gitignore`. Execute queries directly via `./trace_processor` (on Windows, explicitly invoke `python trace_processor`).

> **Important:** The file served at this URL is a `~10KB` Python wrapper script. Don't assume the download failed because it is human-readable text. This is the intended behavior. This script handles lazy-loading the precompiled binary automatically on its first run. Use it directly.

### Step 1: Dissection and Schema Research

1. Identify the core question, required data points, and filtering conditions.
2. **Precedence Rule:** If the user's request contains a SQL query, use it **without modification** and skip to Step 2 for validation.
3. **Mandatory Schema and Module Search:** For every table or view you plan to use, you MUST find its schema in [`perfetto-stdlib.md`](references/perfetto-stdlib.md). **Don't read the entire documentation file** --- it consumes the context window. Follow this precise workflow:
   - **Discovery and Search:** Use available search tools (`grep`, `read_file` or file search) with line limits to discover relevant views, tables or modules based on your problem domain and high-level intents (for example, 'CPU time', 'running time', 'overlap', 'jank').
     - **Why:** Searching solely for exact table names misses comprehensive, pre-computed views built for these analyses.
     - **Note:** You must verify if a Standard Library module already provides the needed abstraction before drafting manual arithmetic or custom functions.
   - **Targeted Bounded Reads:** Once you identify the relevant modules, efficiently read the tables and views within that module section.
   - **Extract:** Extract only the schema, columns, and the exact `INCLUDE
     PERFETTO MODULE` statements for the required object from the documentation.
   - **Verify:** Review the columns, types, and descriptions to ensure the table matches your needs.
4. Print the research results before drafting the query:
5. *Tables/Views:* `Schema for {name}:` listing columns and types.

### Step 2: Draft and Validate Loop (Max 3 Iterations)

Draft the SQL query in SQLite syntax using **only** the schemas retri