Skip to main content

Query Examples

This page provides practical, runnable query examples that you can paste directly into the Datafi Data View query panel. Each example notes which connectors support it.

Basic SELECT​

SELECT customer_id, first_name, last_name, email
FROM customers
LIMIT 100

Works on: All connectors.

Filtered Queries​

SELECT order_id, customer_id, total_amount, status
FROM orders
WHERE total_amount > 500
  AND status = 'completed'

Works on: All connectors.

Pattern Matching​

SELECT customer_id, first_name, email
FROM customers
WHERE email LIKE '%@gmail.com'

Works on: All connectors.

Date Range​

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date < '2026-01-01'
ORDER BY order_date ASC

Works on: All connectors.

JOIN Queries​

CSV Limitation

JOIN queries are not supported on CSV datasources. All other connectors support them.

Inner Join​

SELECT orders.order_id, customers.first_name, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
ORDER BY orders.order_date DESC
LIMIT 50

Left Join​

SELECT customers.customer_id, customers.first_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LIMIT 100

Multi-Table Join​

SELECT
  orders.order_id, customers.first_name,
  products.product_name, order_items.quantity
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id
LIMIT 50

Works on (all JOINs): MSSQL, Snowflake, Databricks, Oracle, MySQL, NetSuite, PostgreSQL, BigQuery, Salesforce, Dynamics.

Aggregation Queries​

SELECT
  COUNT(order_id) AS total_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'completed'

Works on: All connectors.

Group By​

SELECT
  region,
  COUNT(order_id) AS order_count,
  SUM(total_amount) AS total_revenue
FROM orders
GROUP BY region
ORDER BY total_revenue DESC

Works on: All connectors except CSV.

Subqueries​

Filter with a Subquery​

SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE total_amount > 5000
)

Scalar Subquery​

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC
LIMIT 25

Works on (subqueries): MSSQL, Snowflake, Databricks, Oracle, MySQL, NetSuite, PostgreSQL, BigQuery.

Database-Specific Examples​

Snowflake: Monthly Aggregation​

SELECT DATE_TRUNC('month', order_date) AS order_month,
  COUNT(order_id) AS order_count, SUM(total_amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month ASC

PostgreSQL: String Concatenation​

SELECT customer_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM customers
WHERE email IS NOT NULL
LIMIT 50

BigQuery: Timestamp Formatting​

SELECT order_id, FORMAT_TIMESTAMP('%Y-%m-%d', order_date) AS formatted_date
FROM orders
WHERE order_date >= TIMESTAMP('2025-01-01')
LIMIT 100

MSSQL: Automatic LIMIT Translation​

SELECT order_id, total_amount FROM orders ORDER BY total_amount DESC LIMIT 10

Datafi compiles this to SELECT TOP 10 when targeting an MSSQL connector.

Next Steps​