Query and export

Three ways to get answers out of a dataset, from quickest to most flexible: filter the grid, run a read-only SQL query on the dataset page, or ask Jason in chat. Exports give you the whole thing as a file.

Run query

Press Run query in the dataset header. The dialog is titled Run SQL and carries a green Read only pill: nothing you type here can change the data.

Relation names

The panel at the top says which version you are querying (“Query Live product offers version v2”) and lists the names to use in your query:

• data is the worksheet you are viewing. It is tagged “current”.
• sheet_{name} is each other worksheet of the same version, for example sheet_offers or sheet_menu_items. Names are lower-cased with punctuation replaced by underscores.

You can join worksheets by these names. You cannot reference other datasets; to combine datasets, build a dashboard (whose widgets can join across confirmed relationships) or ask Jason.

The editor

Rules the server applies:

• Exactly one SELECT or VALUES statement. Anything else, including a second statement after a semicolon, is rejected with the parser’s message.
• Only the listed relation names.
• Results are capped at 200 rows and 2 MB. Use ORDER BY and LIMIT to make sure the rows you want are the ones that come back.
• Queries are not saved. The editor resets to the default when you reopen the dialog or change version or worksheet. Keep useful queries in your own notes.

Ask Jason

Press Ask Jason in the editor toolbar, type what you want to see, and press Generate. Jason reads the column names and types of every worksheet in the version, writes one query, and puts it in the editor for you to read and run. Edit it if it is not quite right; Jason cannot see the values in the rows, only the columns.

Average current price and product count per brand, brands with the most products first

Jason fills the editor with a GROUP BY brand query ordered by count. Press Run query to see the rows.

If Jason cannot produce a safe query from your description, the dialog says “Jason could not generate a safe query. Try a more specific description.” Naming the columns you mean usually fixes it.

Results

Results appear under the editor with a row count, a resizable table, and an Export button that downloads the result as a CSV named after the dataset, query and version (live-product-offers-query-v2.csv). An empty result reads “The query returned no rows.”

Example queries

These run against the Live product offers example dataset (/datasets/marketdata01) that new workspaces start with. Its columns include product_id, product_name, brand, category, current_price, previous_price, availability, retailer_count and review_score, plus a link column per retailer. Swap in your own column names.

Which brands have the most products, and what do they cost on average?

SELECT brand,
       COUNT(*) AS products,
       ROUND(AVG(current_price)::numeric, 2) AS avg_price
FROM data
GROUP BY brand
ORDER BY products DESC
LIMIT 20

Which products dropped in price, and by how much?

SELECT product_name,
       brand,
       previous_price,
       current_price,
       ROUND(((current_price - previous_price) / previous_price * 100)::numeric, 1) AS change_pct
FROM data
WHERE current_price < previous_price
ORDER BY change_pct ASC
LIMIT 25

How many products are in each availability state?

SELECT availability, COUNT(*) AS products
FROM data
GROUP BY availability
ORDER BY products DESC

A category summary: count, price range and average review.

SELECT category,
       COUNT(*) AS products,
       MIN(current_price) AS cheapest,
       MAX(current_price) AS priciest,
       ROUND(AVG(review_score)::numeric, 2) AS avg_review
FROM data
GROUP BY category
ORDER BY products DESC

Well-reviewed products under $50.

SELECT product_name, brand, current_price, review_score
FROM data
WHERE current_price < 50 AND review_score >= 4.5
ORDER BY review_score DESC, current_price ASC
LIMIT 20

Low-stock products sold by the most retailers.

SELECT product_name, brand, retailer_count
FROM data
WHERE availability = 'Low stock'
ORDER BY retailer_count DESC
LIMIT 20

Column formats are display settings. A currency column holds a plain number, so compare it with < 50, not < '$50'. Text columns are case sensitive in =; use ILIKE '%oled%' for a case-insensitive match.

Export

Exports are in the header menu on the dataset page, and on a pipeline’s Latest outputs tab.

JSON export is available by adding ?export_format=json to the dataset page’s address.

1. Choose the export. A toast reads “Preparing export…” and both export items are disabled until it finishes.
2. Wait. The file is built in the background; the page checks every couple of seconds. Large datasets take longer. You can keep using the page.
3. Download. When the toast says “Export ready” the browser downloads the file, named after the dataset. The file is removed from our side once it has been downloaded.

An export you did not download stays available for 24 hours; after that the toast reads “The generated file is no longer available; request it again.” If the export fails, the toast says so and you can try again.

For a recurring export, do not export by hand: connect a destination such as Google Sheets or a webhook and every new version is delivered automatically. See Sheets, warehouses and webhooks.

Asking Jason in chat

For questions that need reasoning across versions, datasets or time, ask Jason in the workspace chat or in the sidebar on the dataset page. Jason can read any dataset in the workspace, run queries on your behalf, compare versions, and reply with a table or a summary. It does not change anything unless you ask it to.

In Live product offers, which products went from In stock to Low stock since last week, and which retailers list them?

Jason compares the two versions on product_id, lists the products whose availability changed, and includes the retailer links from the row. It offers to set up an alert rule if you want to hear about this automatically.

Selecting rows in the grid before you ask pins them for Jason, so “why do these three have no brand?” refers to exactly those rows. See Chatting with Jason.

What’s next