Sign in
An image of the Stripe logo
Create account
Sign in
Home
Payments
Business operations
Financial services
Developer tools
Security
All products
Home
Payments
Business operations
Home
Payments
Business operations
Financial services
Developer tools
Support
Overview
Overview
Get started
Access data in your Stripe Dashboard with Sigma
Access data in your data warehouse with Data Pipeline
Data freshness
Query data
Write queries
Query transactional data
Query Billing data
Query Connect data
Query Issuing data
Schedule queries
HomeBusiness operationsStripe data

Write queries

Use standard ANSI SQL and a broad range of reporting functions.

Anyone on your account with permission to view reports can write queries. To execute your query and see the results, click Run. More complex queries might take a few moments longer to complete and display results. Attempting to run an invalid query generates an error message that contains the line number and position of the error.

The following query uses the balance_transactions table to get information about the five most recent balance transactions related to refunds.

select date_format(created, '%Y-%m-%d') as day, id, amount, currency, source_id from balance_transactions where type = 'refund' order by day desc limit 5

Each row corresponds to a particular balance transaction item, along with the requested information about them.

dayidamountcurrencysource_id
6/26/2022txn_ZvcxNLkhr0HCBR1-1,000usdre_tIi8W1qqNRgt6YW
6/26/2022txn_oCOq7BrCOhVKrPV-1,000usdre_CnxBNrApLwFd060
6/26/2022txn_iE7t8BfEf4Nz2D6-1,000usdre_jzKniphKH5XEulw
6/26/2022txn_WWApBMmfEks23cY-1,000eurre_BivE7CSkgnJtbxU
6/26/2022txn_F0WMRw8GdDdTDD6-1,000usdre_Em1afBOzzCNscQn

Viewing and downloading query results

Scheduling queries

You can schedule your queries on a daily, weekly, or monthly basis. We send results in an email or a webhook event.

Query results display below the editor, and you can view a maximum of 1,000 results. You can sort the results by clicking on the column headers. You can also resize each column to make it easier to read the results, or adjust the height of the results output.

Amounts express in the lowest available currency unit, such as cents for USD or yen for JPY. For example, an amount of 1,000 with a currency of usd would equate to 10 USD.

You can download your query results in CSV format and use them in spreadsheet applications or other reporting tools. Downloading a CSV includes all query results, so you can work with more than 1,000 results if necessary.

Query history You can view all previously performed queries within the History tab. Selecting a previously-run query loads it into the query editor, ready to be run again.

Templates

A selection of example queries are available in the Templates tab. These queries use common metrics and reports, and serve as starting points for creating your own reports. Selecting a template loads it into the editor, ready to be run.

You can’t modify templates themselves—Click Duplicate to create a copy that you can then edit and save.

Joining tables

You can join columns of type Primary key or Foreign key to similar columns in other tables:

  • Primary key represents the unique identifier (ID) for each record in a table
  • Foreign key represents data that refers to the primary key of another table

For instance, you can join the charge_id​ column of the disputes​ table (a foreign key) to the id​ column of the charges​ table (a primary key).

Sigma and Data Pipeline provide many opportunities to join multiple tables together, depending on the data you want to report on. For instance, you can modify the balance transaction query from earlier to join with the refunds table to provide further information.

select date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day, balance_transactions.amount, balance_transactions.currency, balance_transactions.source_id, refunds.charge_id from balance_transactions inner join refunds -- Joining these tables to retrieve additional information on balance_transactions.source_id=refunds.id where balance_transactions.type = 'refund' order by day desc limit 5

This extended query now returns the original charge ID that the refund relates to.

dayamountcurrencysource_idcharge.id
6/26/2022-1,000usdre_elHCn81h2foyMOEch_AGs8mJnIZGlx0tS
6/26/2022-1,000usdre_gw2GERo5jv5Frf2ch_dSWgf8rerLu4ofY
6/26/2022-1,000usdre_MFtzCCnULkAKVWvch_2rO3n5qv0gbk6rz
6/26/2022-1,000eurre_WgmKxRsvf4mxgcZch_Le07MCPIxS7B5wZ
6/26/2022-1,000usdre_sOnqOD38wT2jtBoch_2ny7TpDx67xAopB

Saving queries

To save a query, enter a name by clicking on the title of the query (labeled as Untitled if it’s a new query), then click Save.

You can find saved queries within the Saved tab. Queries that you’ve saved are listed in the You section. The All section includes queries saved by all team members. Selecting a saved query fills in the query editor automatically—the query doesn’t execute until you click Run.

Sharing queries

The queries you save are also added to the All section and made available to every team member on your account. Each saved query is also given a unique URL that you can share by clicking Copy link. You can use this link as a shortcut to a particular report you regularly use, or share it directly to other team members on the Stripe account.

You can only share queries with team members. Shared queries are also read-only, so other team members can’t modify the queries you create. If a team member wants to make changes to the query, they can save a copy and edit it accordingly.

Was this page helpful?
Questions? Contact us.
View developer tutorials on YouTube.
Check out our product changelog.
Powered by Markdoc
You can unsubscribe at any time. Read our privacy policy.
On this page
Templates
Saving queries
Stripe Shell
Test mode
▗▄ ▄▟█ █▀▀ ▗▟████▙▖ ██████ ███▗▟█ ███ ███▗▟██▙▖ ▗▟█████▙▖ ███▖ ▀▀ ███ ███▀▀▀ ███ ███▀ ███ ███ ███ ▝▜████▙▖ ███ ███ ███ ███ ███ █████████ ▄▄ ▝███ ███ ▄ ███ ███ ███▄ ███ ███ ▄▄ ▝▜████▛▘ ▝▜███▛ ███ ███ ███▝▜██▛▘ ▝▜█████▛▘ ███ ▀▘
Welcome to the Stripe Shell! Stripe Shell is a browser-based shell with the Stripe CLI pre-installed. Login to Stripe docs and press Control + Backtick on your keyboard to start managing your Stripe resources in test mode. - View supported commands: - Find webhook events: - Listen for webhook events: - Call Stripe APIs: stripe [api resource] [operation] (e.g. )
The Stripe Shell is best experienced on desktop.
$