Query examples

Esta página aún no está disponible en tu idioma.

These examples use fictional table and column names. They target the SQL workbench and assume one row per order or session as described. Review the schema, timezone, currency, and metric definition before using them with your data.

Monthly revenue by country

Assume revenue is already in one reporting currency and order_date is a date.

SELECT country, SUM(revenue) AS revenue
FROM lake.uploads.orders
WHERE order_date >= DATE '2026-09-01'
  AND order_date < DATE '2026-10-01'
  AND status = 'completed'
GROUP BY country
ORDER BY revenue DESC
LIMIT 100;

Returns September 2026 completed-order revenue by country. Define how discounts, refunds, and tax affect the revenue column before comparing it with finance reports.

Conversion rate by channel

Assume one row per session and a Boolean converted column. The decimal multiplier avoids integer division.

SELECT channel,
       COUNT(*) AS sessions,
       SUM(CASE WHEN converted = TRUE THEN 1 ELSE 0 END) AS conversions,
       100.0 * SUM(CASE WHEN converted = TRUE THEN 1 ELSE 0 END)
         / NULLIF(COUNT(*), 0) AS conversion_rate_percent
FROM lake.uploads.sessions
WHERE session_date >= DATE '2026-09-01'
  AND session_date < DATE '2026-10-01'
GROUP BY channel
ORDER BY conversion_rate_percent DESC
LIMIT 100;

A null conversion flag counts as not converted in this example. If null means missing tracking, exclude or report those sessions separately. Compare sample sizes as well as rates.

Customer spend without multiplying rows

Aggregate the order table before joining to a customer dimension. The customer table must still contain one row per customer ID.

WITH spend AS (
  SELECT customer_id, SUM(revenue) AS revenue
  FROM lake.uploads.orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT c.customer_id, c.segment, COALESCE(s.revenue, 0) AS revenue
FROM lake.uploads.customers AS c
LEFT JOIN spend AS s ON c.customer_id = s.customer_id
ORDER BY revenue DESC
LIMIT 100;

This includes customers with no completed orders and reports their spend as zero. Add a date filter inside the CTE for a period-specific result.

Check duplicate business keys

SELECT order_id, COUNT(*) AS rows_per_order
FROM lake.uploads.orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY rows_per_order DESC
LIMIT 100;

Multiple rows can be valid on an order-line or event table. Decide the intended row granularity before treating duplicates as an error.