Basin

POST https://api.sql.cloudflarestorage.com/api/v1/accounts/{ACCOUNT_ID}/basin-sql/query/{BUCKET}
Authorization: Bearer <token>
Content-Type: application/json
Body: {"query": "<SQL>"}

{
  "result": {
    "request_id": "dqe-prod-01...",
    "schema": [{"name": "cnt", "descriptor": {"type": {"name": "int64"}, "nullable": false}}],
    "rows": [{"category": "Electronics", "cnt": 12345}],
    "metrics": {"r2_requests_count": 5, "files_scanned": 29, "bytes_scanned": 12345678, "cache_hits": 0}
  },
  "success": true, "errors": []
}

SELECT [DISTINCT] columns | expressions | aggregations
FROM namespace.table [alias]
[ [INNER|LEFT|RIGHT|FULL OUTER|CROSS] JOIN namespace.table2 alias2 ON ... ]
[WHERE ...] [GROUP BY ...] [HAVING ...]
[QUALIFY window_predicate]
[ORDER BY expr [ASC|DESC]]
[LIMIT n]                          -- default 500, max 10,000

SHOW DATABASES;            -- list namespaces (aliases: SHOW NAMESPACES / SHOW SCHEMAS)
SHOW TABLES IN namespace;
DESCRIBE namespace.table;  -- columns, types, partition keys
EXPLAIN [FORMAT JSON] SELECT ...;   -- execution plan (free; no data scanned)

-- JOINs: all types + multi-way
SELECT z.domain, COUNT(*) AS cnt
FROM ns.zones z
INNER JOIN ns.http_requests h ON z.zone_id = h.zone_id
LEFT  JOIN ns.firewall_events f ON z.zone_id = f.zone_id
GROUP BY z.domain ORDER BY cnt DESC LIMIT 20;

-- Subqueries: IN / EXISTS / scalar / derived
SELECT * FROM ns.t1 WHERE id IN (SELECT id FROM ns.t2 WHERE x > 0);
SELECT col, (SELECT COUNT(*) FROM ns.t2 s WHERE s.id = t.id) AS cnt FROM ns.t1 t;

-- Multi-table CTE with JOIN
WITH top AS (SELECT zone_id, COUNT(*) AS req FROM ns.http_requests GROUP BY zone_id ORDER BY req DESC LIMIT 50)
SELECT t.zone_id, t.req FROM top t LEFT JOIN ns.zones z ON t.zone_id = z.zone_id;

-- Set ops: UNION / UNION ALL / INTERSECT / EXCEPT
SELECT zone_id FROM ns.firewall_events WHERE action = 'block'
UNION SELECT zone_id FROM ns.http_requests WHERE risk_score > 0.8;

SELECT event_id,
       ROW_NUMBER() OVER (PARTITION BY mag_type ORDER BY magnitude DESC) AS rn,
       LAG(magnitude, 2, 0.0) OVER (ORDER BY occurred_at) AS prev2,   -- offset + default
       NTH_VALUE(magnitude, 2) OVER (ORDER BY magnitude DESC) AS n2,
       SUM(magnitude) OVER (ORDER BY occurred_at) AS running,
       AVG(magnitude) OVER (ORDER BY magnitude ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM ns.earthquakes;

-- QUALIFY: filter on a window result (top row per partition)
SELECT event_id, mag_type, magnitude FROM ns.earthquakes
QUALIFY ROW_NUMBER() OVER (PARTITION BY mag_type ORDER BY magnitude DESC) = 1;

WHERE status = 200 AND method = 'GET'              -- not '200', not GET
  AND ts >= '2026-01-01T00:00:00Z'                 -- not '2026-01-01'

SELECT pricing['price'] AS price, get_field(pricing, 'discount') AS disc FROM ns.t;  -- struct
SELECT tags[1] AS first_tag, array_length(tags) AS n FROM ns.t;                       -- array (1-indexed)
SELECT map_keys(meta), map_extract(meta, 'source') FROM ns.t;                         -- map