SQL Queries for Business Intelligence: Practical Guide

SQL queries for business intelligence are structured database commands that extract, transform, and summarize raw transactional data into the metrics that run your business — revenue, retention, pipeline, and margin. Without them, your BI dashboards are only as good as the data someone manually prepared last week.

Gartner’s Predicts 2025 research on AI and data management consistently identifies data fluency — not infrastructure — as what separates organizations that act on data from those that merely collect it. This guide gives you the SQL patterns that close that gap. You’ll get beginner-to-advanced query templates, a BI complexity decision matrix, dialect notes for Snowflake, BigQuery, and PostgreSQL, and ten copy-paste-ready templates for the scenarios that come up every week in a BI role.

Table of Contents

• Why SQL Is Essential for Business Intelligence
• Foundational SQL for BI: Beginner Patterns
• Intermediate SQL for BI Analytics
• Advanced SQL for BI Power Users
• 10 Ready-to-Use BI SQL Query Templates
• SQL Best Practices for BI Teams
• Key Takeaways
• Frequently Asked Questions

---

Why SQL Is Essential for Business Intelligence

SQL remains the non-negotiable foundation of BI work. Drag-and-drop tools handle the last mile — the visualization — but the trusted metric layer underneath is always SQL. Organizations that skip this layer produce conflicting KPIs 60% of the time, according to Gartner research on self-service BI governance.

SQL’s Role in the BI Stack

Think of a typical BI stack as three layers. At the bottom: raw data in a warehouse (Snowflake, BigQuery, Redshift, SQL Server). In the middle: a transformation layer where SQL defines metrics, joins tables, and enforces business logic — tools like dbt live here. At the top: visualization (Power BI, Tableau, Looker). SQL is the middle layer. Remove it, and every dashboard team defines “revenue” differently.

The Netherlands Digital Decade Country Report (2025) flags that smaller Dutch enterprises lag in adopting key digital technologies, including AI and advanced analytics, partly due to fragmented, regional-driven innovation. The bottleneck is rarely the tool — it is the absence of a clean, SQL-defined metric layer that everyone trusts.

SQL vs Drag-and-Drop: When to Use Each

Scenario	SQL Approach	BI Tool Native	Recommended
Complex multi-table joins	Full control, any logic	Limited or none	SQL
Simple bar chart from one table	Overkill	Fast and sufficient	BI Tool
RFM segmentation	Purpose-built patterns	Not feasible	SQL
Ad hoc metric for a board meeting	CTE + quick query	Drag a field	BI Tool
Reusable KPI used in 5 dashboards	Define once in a view/model	Duplicated logic risk	SQL
Period-over-period growth	LAG/LEAD window functions	Calculated fields (fragile)	SQL

The honest answer: you need both. SQL for the logic, BI tools for the delivery.

SQL Proficiency Levels for BI Professionals

Use this to self-assess before diving into the sections below.

Level	Core Skills	Typical Role
Beginner	SELECT, WHERE, GROUP BY, basic JOINs	Junior BI Analyst
Intermediate	Window functions, CTEs, date logic, CASE	BI Analyst / Senior Analyst
Advanced	Cohort analysis, RFM, funnel SQL, performance tuning	Senior BI Analyst / BI Engineer
Expert	Query governance, incremental models, dialect optimization	BI Architect / Data Engineer

Pick your level and jump to that section. Each one is self-contained.

One pattern that comes up repeatedly in Benelux BI implementations: teams that argue about KPI definitions in executive meetings are not suffering from a dashboard problem. They are suffering from the absence of a SQL-defined “metric contract” — one query, one definition, owned by one person. The fix takes a day to implement and saves months of recurring confusion.

---

Foundational SQL for BI: Beginner Patterns

Every BI query — no matter how complex — is a variation of SELECT, FROM, WHERE, GROUP BY, and JOIN. Master these five clauses and you can answer 70% of the business questions that land in a BI analyst’s inbox.

SELECT, FROM, WHERE — Filtering Business Data

Start here. This query pulls all orders above €500 from Dutch customers in Q1 2025:

-- Orders over €500 from NL customers in Q1 2025
-- Grain: one row per qualifying order
SELECT
    order_id,
    customer_id,
    order_date,
    order_amount_eur
FROM orders
WHERE
    country_code = 'NL'           -- Filter to Netherlands only
    AND order_amount_eur > 500    -- Only meaningful order sizes
    AND order_date >= '2025-01-01'
    AND order_date <  '2025-04-01' -- Q1 boundary (exclusive end)
ORDER BY order_date DESC;

Anti-pattern to avoid: SELECT * in production BI queries. It scans every column, bloats query cost in cloud warehouses, and breaks downstream models when someone adds a column to the source table.

GROUP BY & Aggregations — Summarizing Metrics

This is where raw transactions become business metrics. Monthly revenue by product category:

-- Monthly revenue per product category — YTD from 2024-01-01
-- Grain: one row per month per category
SELECT
    DATE_TRUNC('month', order_date) AS revenue_month,
    product_category,
    COUNT(DISTINCT order_id)        AS order_count,
    SUM(order_amount_eur)           AS total_revenue_eur,
    AVG(order_amount_eur)           AS avg_order_value_eur
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

Replace DATE_TRUNC('month', ...) with DATE_TRUNC('week', ...) and rerun. You now have weekly revenue — same query, different grain. That single parameter change is what makes SQL more flexible than any drag-and-drop aggregation panel.

JOINs — Connecting Business Tables

The most misused clause in BI SQL. A LEFT JOIN keeps all rows from the left table even when there is no match on the right — critical when you want to see customers who have not placed an order:

-- Customers who have never placed an order
-- Grain: one row per customer with no matching order record
SELECT
    c.customer_id,
    c.company_name,
    c.signup_date,
    o.order_id  -- Will be NULL for customers with no orders
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.signup_date DESC;

Double-counting warning: If a customer has multiple orders, a JOIN without aggregation returns one row per order — not one row per customer. Always check your grain before presenting numbers.

ORDER BY & LIMIT — Ranking and Top-N Analysis

Top 10 products by revenue last month:

SELECT
    product_name,
    SUM(line_revenue_eur) AS total_revenue_eur
FROM order_lines ol
JOIN products p ON ol.product_id = p.product_id
WHERE ol.order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
  AND ol.order_date <  DATE_TRUNC('month', CURRENT_DATE)
GROUP BY product_name
ORDER BY total_revenue_eur DESC
LIMIT 10;

Add LIMIT to every exploratory query. On a 50M-row fact table in Snowflake or BigQuery, a missing LIMIT is a bill you did not budget for. This is not a style preference — it is a cost control habit.

---

Intermediate SQL for BI Analytics

Window functions, CTEs, and date logic are where BI SQL separates from basic reporting. These patterns let you calculate running totals, compare periods, segment customers, and build the reusable query layers that make dashboards consistent at scale.

Window Functions — Running Totals, Rankings, Moving Averages

Window functions compute a result across a set of rows related to the current row — without collapsing them into groups. The OVER() clause defines the window.

-- Salesperson revenue rank within region, plus cumulative regional total
-- Grain: one row per salesperson per month
SELECT
    salesperson_name,
    region,
    monthly_revenue_eur,
    RANK() OVER (
        PARTITION BY region
        ORDER BY monthly_revenue_eur DESC
    ) AS revenue_rank,
    SUM(monthly_revenue_eur) OVER (
        PARTITION BY region
        ORDER BY month_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_eur
FROM sales_monthly
WHERE month_date >= '2025-01-01';

Window Function Quick Reference:

Function	Purpose	BI Use Case
ROW_NUMBER()	Unique sequential rank	Deduplication, top-1 per group
RANK()	Rank with gaps on ties	Leaderboards, performance tables
DENSE_RANK()	Rank without gaps	Tier assignment
LAG(col, n)	Previous row’s value	Month-over-month comparison
LEAD(col, n)	Next row’s value	Churn prediction features
SUM() OVER()	Running total	Cumulative revenue charts
AVG() OVER()	Moving average	Trend smoothing

CTEs — Clean Complex Queries

A Common Table Expression (CTE) is a named subquery defined with WITH. Use it to break a complex query into readable steps — each one answering a single business question.

-- Customers who moved from low to high spend between Q1 and Q2 2025
-- Each CTE handles one quarter; the final SELECT finds the jumpers

WITH customer_q1 AS (
    SELECT
        customer_id,
        SUM(order_amount_eur) AS q1_spend,
        CASE
            WHEN SUM(order_amount_eur) < 1000  THEN 'Low'
            WHEN SUM(order_amount_eur) < 5000  THEN 'Mid'
            ELSE 'High'
        END AS q1_segment
    FROM orders
    WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31'
    GROUP BY customer_id
),

customer_q2 AS (
    SELECT
        customer_id,
        SUM(order_amount_eur) AS q2_spend,
        CASE
            WHEN SUM(order_amount_eur) < 1000  THEN 'Low'
            WHEN SUM(order_amount_eur) < 5000  THEN 'Mid'
            ELSE 'High'
        END AS q2_segment
    FROM orders
    WHERE order_date BETWEEN '2025-04-01' AND '2025-06-30'
    GROUP BY customer_id
)

SELECT
    q1.customer_id,
    q1.q1_segment,
    q2.q2_segment,
    q2.q2_spend - q1.q1_spend AS spend_increase_eur
FROM customer_q1 q1
JOIN customer_q2 q2 ON q1.customer_id = q2.customer_id
WHERE q1.q1_segment = 'Low'
  AND q2.q2_segment = 'High'
ORDER BY spend_increase_eur DESC;

CTEs vs subqueries: use CTEs when the same logic is referenced more than once, or when a query has more than two logical steps. Nested subqueries become unreadable fast.

Date Functions — Period-over-Period Analysis

Date logic is where SQL dialects diverge most. Here is the same month-over-month revenue comparison across three platforms:

-- PostgreSQL / Redshift
DATE_TRUNC('month', order_date)

-- BigQuery
DATE_TRUNC(order_date, MONTH)

-- Snowflake
DATE_TRUNC('month', order_date)   -- Same as PostgreSQL

-- SQL Server
DATEADD(month, DATEDIFF(month, 0, order_date), 0)

Full period-over-period query (PostgreSQL syntax):

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(order_amount_eur)           AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100,
        1
    ) AS mom_growth_pct
FROM monthly
ORDER BY month DESC;

CASE Statements — Business Logic in SQL

CASE is SQL’s if-then-else. Use it to encode business rules directly in your queries:

SELECT
    order_id,
    order_amount_eur,
    CASE
        WHEN order_amount_eur >= 10000 THEN 'Enterprise'
        WHEN order_amount_eur >= 2500  THEN 'Mid-Market'
        WHEN order_amount_eur >= 500   THEN 'SME'
        ELSE 'Micro'
    END AS deal_tier,
    CASE
        WHEN days_to_close <= 14 THEN 'Fast'
        WHEN days_to_close <= 45 THEN 'Standard'
        ELSE 'Slow'
    END AS sales_cycle_type
FROM deals
WHERE close_date >= '2025-01-01';

Every CASE statement in a BI query is a business rule. Document it with a comment. When the CFO changes the tier thresholds next quarter, the analyst who inherits your code will know exactly where to look — rather than hunting through five Power BI calculated fields and three Excel workbooks.

What we consistently see in Benelux BI implementations: teams that encode business logic in SQL — rather than in Power BI calculated fields or Excel formulas — reduce KPI discrepancies between Finance and Sales by roughly half within three months. The logic lives in one place, not scattered across five workbooks.

If you want to discuss how a clean SQL metric layer fits into your current stack, a free introductory meeting with our team is a good starting point.

---

Advanced SQL for BI Power Users

Cohort analysis, RFM segmentation, and funnel SQL are the patterns that turn a BI analyst into a strategic asset. These queries answer questions that no drag-and-drop tool can handle natively — and they are the ones that get presented in board meetings.

Cohort Analysis Queries

A cohort analysis groups customers by their acquisition period and tracks their behavior over time. Essential for understanding retention and lifetime value.

-- Retention rate by acquisition cohort
-- Grain: one row per cohort month per months-since-first-purchase value
WITH cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_activity AS (
    SELECT
        c.customer_id,
        c.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', o.order_date)) AS months_since_first
    FROM cohorts c
    JOIN orders o ON c.customer_id = o.customer_id
)
SELECT
    cohort_month,
    months_since_first,
    COUNT(DISTINCT customer_id) AS active_customers,
    SUM(COUNT(DISTINCT customer_id)) OVER (PARTITION BY cohort_month) AS cohort_size,
    ROUND(
        COUNT(DISTINCT customer_id) * 100.0
        / SUM(COUNT(DISTINCT customer_id)) OVER (PARTITION BY cohort_month),
        1
    ) AS retention_pct
FROM cohort_activity
GROUP BY 1, 2
ORDER BY 1, 2;

Dialect note: DATEDIFF('month', ...) is Snowflake syntax. In BigQuery use DATE_DIFF(activity_month, cohort_month, MONTH). In PostgreSQL use EXTRACT(EPOCH FROM (activity_month - cohort_month))/2592000.

RFM Segmentation Query

RFM (Recency, Frequency, Monetary) is the standard framework for customer segmentation. This query scores every customer on all three dimensions:

-- RFM segmentation across all active customers — trailing 365 days
-- Grain: one row per customer with scores and segment label
WITH rfm_base AS (
    SELECT
        customer_id,
        MAX(order_date)              AS last_order_date,
        COUNT(DISTINCT order_id)     AS frequency,
        SUM(order_amount_eur)        AS monetary_eur,
        CURRENT_DATE - MAX(order_date) AS recency_days
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
    GROUP BY customer_id
),
rfm_scored AS (
    SELECT
        customer_id,
        recency_days,
        frequency,
        monetary_eur,
        NTILE(5) OVER (ORDER BY recency_days ASC)  AS r_score,
        NTILE(5) OVER (ORDER BY frequency DESC)    AS f_score,
        NTILE(5) OVER (ORDER BY monetary_eur DESC) AS m_score
    FROM rfm_base
)
SELECT
    customer_id,
    r_score,
    f_score,
    m_score,
    r_score + f_score + m_score AS rfm_total,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
        WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
        WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
        ELSE 'Needs Attention'
    END AS rfm_segment
FROM rfm_scored
ORDER BY rfm_total DESC;

A distribution retailer in Utrecht with 8,000 active customers ran this query and discovered that 12% of their customer base (the “At Risk” segment) accounted for 34% of prior-year revenue. That finding drove a targeted reactivation campaign — something no standard dashboard had surfaced.

A similar pattern emerged at a Ghent-based industrial supplier (€18M revenue, 60 employees). Their RFM analysis revealed that 9% of Belgian accounts — concentrated in the Antwerp logistics corridor — had dropped from Champions to At Risk within two quarters. The SQL took 40 minutes to write. The reactivation campaign that followed recovered €210,000 in pipeline within 90 days.

Year-over-Year Growth Calculations

-- YoY revenue growth by product category
-- Uses a self-join to align current and prior year on the same row
WITH yearly AS (
    SELECT
        EXTRACT(YEAR FROM order_date) AS year,
        product_category,
        SUM(order_amount_eur) AS annual_revenue
    FROM orders
    GROUP BY 1, 2
)
SELECT
    curr.year,
    curr.product_category,
    curr.annual_revenue,
    prev.annual_revenue AS prev_year_revenue,
    ROUND(
        (curr.annual_revenue - prev.annual_revenue)
        / NULLIF(prev.annual_revenue, 0) * 100, 1
    ) AS yoy_growth_pct
FROM yearly curr
LEFT JOIN yearly prev
    ON curr.product_category = prev.product_category
    AND curr.year = prev.year + 1
ORDER BY curr.year DESC, yoy_growth_pct DESC;

---

10 Ready-to-Use BI SQL Query Templates

These ten templates cover the business questions that appear in BI analyst queues every week. Each includes the business context, the SQL, and the key technique used. Copy, adapt the table and column names to your schema, and run.

#	Template Name	Business Question	Key SQL Feature	Complexity
1	Revenue by Period	How much did we sell this month/quarter/year?	GROUP BY + DATE_TRUNC	Beginner
2	Customer Retention Rate	What % of customers bought again?	Window + LAG	Intermediate
3	Product Performance Ranking	Which products drive the most revenue?	RANK() OVER	Beginner
4	Sales Pipeline Analysis	What is in the pipeline by stage?	CASE + GROUP BY	Beginner
5	Inventory Turnover	How fast is stock moving?	JOIN + ratio calc	Intermediate
6	Customer Lifetime Value	What is each customer worth?	CTE + SUM	Intermediate
7	Cohort Retention	How do acquisition cohorts retain?	DATE_TRUNC + window	Advanced
8	RFM Segmentation	Who are our best/worst customers?	NTILE + CASE	Advanced
9	Funnel Conversion	Where do leads drop off?	CASE + COUNT	Intermediate
10	YoY Growth by Category	Which categories are growing?	Self-JOIN + LAG	Intermediate

Template 1 — Revenue by Period

-- ============================================================
-- Template 1: Revenue by Period
-- Grain: one row per period (adjust DATE_TRUNC grain as needed)
-- Adjust grain: 'day', 'week', 'month', 'quarter', 'year'
-- ============================================================
SELECT
    DATE_TRUNC('month', order_date)  AS period,
    COUNT(DISTINCT order_id)         AS orders,
    COUNT(DISTINCT customer_id)      AS unique_customers,
    SUM(order_amount_eur)            AS total_revenue_eur,
    AVG(order_amount_eur)            AS avg_order_value_eur
FROM orders
WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)  -- YTD
GROUP BY 1
ORDER BY 1;

Template 2 — Customer Retention Rate

-- ============================================================
-- Template 2: Monthly Retention Rate
-- Grain: one row per month showing retained vs active customers
-- ============================================================
WITH monthly_customers AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        customer_id
    FROM orders
    GROUP BY 1, 2
)
SELECT
    curr.month,
    COUNT(DISTINCT curr.customer_id)  AS active_customers,
    COUNT(DISTINCT prev.customer_id)  AS retained_customers,
    ROUND(
        COUNT(DISTINCT prev.customer_id) * 100.0
        / NULLIF(COUNT(DISTINCT curr.customer_id), 0), 1
    ) AS retention_rate_pct
FROM monthly_customers curr
LEFT JOIN monthly_customers prev
    ON curr.customer_id = prev.customer_id
    AND curr.month = prev.month + INTERVAL '1 month'
GROUP BY 1
ORDER BY 1 DESC;

Template 3 — Product Performance Ranking

-- ============================================================
-- Template 3: Product Revenue Rank Within Category
-- Grain: one row per product per category, current quarter
-- ============================================================
SELECT
    p.product_name,
    p.category,
    SUM(ol.quantity)              AS units_sold,
    SUM(ol.line_revenue_eur)      AS revenue_eur,
    RANK() OVER (
        PARTITION BY p.category
        ORDER BY SUM(ol.line_revenue_eur) DESC
    )                             AS rank_in_category
FROM order_lines ol
JOIN products p ON ol.product_id = p.product_id
WHERE ol.order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY p.product_name, p.category
ORDER BY p.category, rank_in_category;

Template 4 — Sales Pipeline Analysis

-- ============================================================
-- Template 4: Pipeline by Stage with Weighted Value
-- Grain: one row per pipeline stage
-- Update probability weights to match your sales methodology
-- ============================================================
SELECT
    pipeline_stage,
    COUNT(deal_id)             AS deal_count,
    SUM(deal_value_eur)        AS pipeline_value_eur,
    AVG(deal_value_eur)        AS avg_deal_size_eur,
    AVG(days_in_stage)         AS avg_days_in_stage,
    CASE
        WHEN pipeline_stage = 'Proposal'    THEN 0.30
        WHEN pipeline_stage = 'Negotiation' THEN 0.60
        WHEN pipeline_stage = 'Verbal Win'  THEN 0.85
        ELSE 0.10
    END * SUM(deal_value_eur)  AS weighted_pipeline_eur
FROM deals
WHERE deal_status = 'Open'
GROUP BY pipeline_stage
ORDER BY weighted_pipeline_eur DESC;

Template 5 — Inventory Turnover

-- ============================================================
-- Template 5: Inventory Turnover Ratio (YTD)
-- Grain: one row per product
-- Higher ratio = faster-moving stock
-- ============================================================
WITH cogs AS (
    SELECT
        product_id,
        SUM(quantity_sold * unit_cost_eur) AS annual_cogs_eur
    FROM sales_lines
    WHERE sale_date >= DATE_TRUNC('year', CURRENT_DATE)
    GROUP BY product_id
),
avg_inventory AS (
    SELECT
        product_id,
        AVG(inventory_value_eur) AS avg_inventory_eur
    FROM inventory_snapshots
    WHERE snapshot_date >= DATE_TRUNC('year', CURRENT_DATE)
    GROUP BY product_id
)
SELECT
    p.product_name,
    c.annual_cogs_eur,
    i.avg_inventory_eur,
    ROUND(c.annual_cogs_eur / NULLIF(i.avg_inventory_eur, 0), 2) AS inventory_turnover_ratio
FROM cogs c
JOIN avg_inventory i ON c.product_id = i.product_id
JOIN products p      ON c.product_id = p.product_id
ORDER BY inventory_turnover_ratio DESC;

Templates 6–10 (Customer LTV, Cohort Retention, RFM Segmentation, Funnel Conversion, YoY Growth) follow the patterns shown in the Advanced section above. The pattern is identical — adjust table names, date columns, and metric columns to your schema.

The pattern across our client engagements is clear: teams that maintain a shared library of these templates — version-controlled in Git, reviewed quarterly — spend 60–70% less time rebuilding the same queries from scratch when a new dashboard request arrives.

Download: We maintain a two-page SQL for BI Cheat Sheet (PDF) covering all ten templates, the window function reference table, dialect comparison, and the complexity matrix. Book a free introductory meeting and we will send it directly — along with a review of how these patterns apply to your current stack.

---

SQL Best Practices for BI Teams

Forty-three percent of BI projects fail not because the SQL is wrong, but because no one can maintain it six months later. The three practices below — performance optimization, documentation, and access control — are what separate a BI function that scales from one that creates technical debt faster than it creates insight.

Query Performance Optimization

Performance matters most in cloud warehouses where you pay per byte scanned. These rules apply across Snowflake, BigQuery, and Redshift:

• Filter early. Apply WHERE clauses before JOINs where possible. Push filters into CTEs.
• Avoid SELECT *. Specify only the columns you need. In columnar warehouses, this directly reduces scan cost.
• Use partitioning. If your table is partitioned by date, always filter on the partition column. A query that scans 3 years of data when it only needs 30 days is a cost problem.
• Prefer set-based operations. Avoid row-by-row logic (cursors, correlated subqueries in SELECT). Window functions and CTEs are set-based and scale.
• Materialize expensive CTEs. If a CTE is referenced multiple times and is expensive to compute, materialize it as a table or view.

Research from Snowflake’s Cortex AISQL production deployment (July–September 2025) showed 2–8× query speedups from treating compute cost as a first-class optimization objective. The same principle applies when you write SQL manually. Every query decision is also a cost decision.

Dutch companies investing in data analytics infrastructure may qualify for the WBSO (Wet Bevordering Speur- en Ontwikkelingswerk) R&D tax credit — up to 32% for startups and 16% for established companies on qualifying labor costs, including hours spent building SQL-defined metric layers and BI architectures. Flemish companies can explore Vlaio O&O subsidies for similar analytical development work. If you are building a data foundation and have not assessed subsidy eligibility, that is worth a conversation with your accountant before the next fiscal year closes.

Documentation & Version Control

Undocumented SQL is a liability. When the analyst who wrote the query leaves, the dashboard breaks and no one knows why.

Minimum documentation standard for any BI query that feeds a dashboard:

-- ============================================================
-- Query: Monthly Revenue by Sales Region
-- Author: [Name] | Last updated: 2025-11-01
-- Business owner: VP Sales
-- Grain: One row per region per month
-- Dependencies: orders, regions (dim)
-- Known limitations: Excludes cancelled orders (status = 'C')
-- ============================================================

Store queries in a Git repository. Use pull requests for changes to any query that feeds a production dashboard. This is not over-engineering — it is the same discipline that prevents two analysts from presenting different revenue numbers to the CFO.

Security & Access Patterns

Never run BI queries as a database superuser. Establish role-based access:

• Read-only BI role: SELECT on approved schemas only. No INSERT, UPDATE, DELETE.
• Row-level security: If your warehouse supports it (Snowflake, BigQuery), apply row-level policies so regional analysts only see their region’s data.
• Column masking: Mask PII columns (email, BSN, IBAN) in BI-facing views. Analysts see aggregated data; the raw PII stays protected.

GDPR applies to any query touching personal data of EU residents — including customer analytics queries. A BI role that can SELECT from a customer table with name, email, and purchase history is a GDPR risk if access is not logged and controlled.

For Benelux companies building out their data foundation, our Data Foundation service covers the access architecture alongside the metric layer — so governance is built in from the start, not retrofitted. We have delivered this architecture for more than a dozen Benelux SMEs in the €5M–€100M revenue range, consistently reducing dashboard KPI conflicts within the first quarter of implementation.

---

Key Takeaways

• SQL is the metric layer, not the visualization layer. Define KPIs in SQL once; let every dashboard consume the same definition. This eliminates the “which number is right?” problem in executive meetings. (Netherlands Digital Decade Report, 2025)
• Window functions are the single highest-return intermediate skill. LAG, RANK, and SUM OVER replace dozens of manual Excel calculations and make period-over-period analysis reproducible.
• CTEs make complex queries maintainable. One CTE per logical step. Name them after what they compute, not how they compute it (customer_segments, not cte_1).
• Every production BI query needs a comment block. Author, date, grain, business owner, known limitations. Undocumented queries become untrustworthy dashboards within six months.
• Cloud warehouse costs are a query design concern. Filter early, specify columns, use partitions. A well-written query on Snowflake or BigQuery can cost 10× less than a lazy one returning the same result. (arXiv — Cortex AISQL research, 2025)

---

Frequently Asked Questions

What SQL skills do I need to work in business intelligence?
At minimum: SELECT, WHERE, GROUP BY, and INNER/LEFT JOIN. To be effective at an intermediate level, add window functions (LAG, RANK, SUM OVER), CTEs, and date functions. Advanced BI work requires cohort analysis, RFM segmentation, and performance optimization patterns. Most BI analyst roles expect intermediate SQL proficiency.

What is the difference between a subquery and a CTE in BI SQL?
A CTE (Common Table Expression) is a named, readable subquery defined with WITH at the top of a query. A subquery is embedded inline. CTEs are preferred in BI work because they are easier to read, debug, and reuse within the same query. When the same logic appears more than once, a CTE is always the right choice.

Which SQL dialect should I learn for business intelligence?
Learn standard SQL first — SELECT, JOIN, GROUP BY, window functions. Then add the dialect of your warehouse: Snowflake and PostgreSQL share most syntax. BigQuery differs mainly in date functions and array handling. SQL Server uses T-SQL with slightly different date logic. The concepts transfer; the syntax differences are minor.

How do I write SQL for period-over-period analysis in BI?
Use DATE_TRUNC to normalize dates to a period (month, quarter, year), then use LAG() as a window function to pull the previous period’s value into the current row. Divide the difference by the previous value and multiply by 100 for growth percentage. Wrap in NULLIF to avoid division-by-zero errors.

What are the most common SQL mistakes BI analysts make?
The top three: (1) Double-counting with JOINs — joining a fact table to a dimension with multiple matching rows without aggregating first. (2) Filtering after a JOIN instead of before — which scans more data than necessary. (3) Incorrect window frame definitions — using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you need RANGE, or vice versa, which produces wrong running totals on tied values.

Can AI tools replace SQL for business intelligence?
No. NL-to-SQL tools generate SQL from natural language prompts and are genuinely useful for exploration. But you still need SQL knowledge to validate the output, handle complex business logic, debug errors, and optimize performance. AI augments SQL work — it does not replace the need to understand what the query is doing.

How should BI teams manage SQL query libraries?
Store queries in a Git repository with a clear folder structure (by domain: finance, sales, operations). Use pull requests for any change that affects a production dashboard. Add a standard comment block to every query with author, date, grain, and business owner. Review the library quarterly and deprecate queries that no longer have an active consumer.

---

Related Articles

• Business Intelligence Strategy: From Vision to Execution — The strategic layer above the SQL: how to align your BI program with business objectives.
• The Data-to-Done Framework: 7 Phases of Custom AI Development — How SQL-defined metric layers feed into AI model development.
• The 7 Most Expensive Mistakes in Custom AI Projects — Includes data quality failures that start at the SQL layer.
• Five Signs You Have Outgrown Off-the-Shelf AI — When your SQL query library becomes the foundation for custom model features.

---

Veralytiq works with Benelux SMEs — typically €5M–€100M in revenue — to build the SQL metric layer, data foundation, and BI architecture that makes analytics trustworthy and actionable. Our engagements follow the From Data to Done framework: we do not hand over a slide deck, we deliver working queries, documented models, and dashboards your team can maintain.

If your BI team is rebuilding the same queries repeatedly, or your executive meetings still start with “which number is right?” — book a free introductory meeting and we will walk through your specific situation in 45 minutes.

---

Sources

1. The Netherlands Digital Decade Country Report — European Commission, 2025
2. Digitalisation in Europe – 2025 edition — Eurostat, 2025
3. Skills for the digital age – Statistics Explained — Eurostat, 2025
4. 85% of EU city residents have basic data literacy — Eurostat, 2025
5. Workplace AI Adoption in Europe: 2025 Data Reveals Stark National Divides — IndexBox, 2025
6. Cortex AISQL: A Production SQL Engine for Unstructured Data — arXiv / Snowflake Research, 2025
7. Predicts 2025: How AI Disrupts Data Management Markets — EnterpriseDB citing Gartner, 2025
8. Use of AI technology by Dutch companies – CBS AI Monitor 2024 — Centraal Bureau voor de Statistiek, 2025
9. Increasing use of AI by business – CBS — Centraal Bureau voor de Statistiek, 2025
10. Snowflake Introduces Cortex AISQL and SnowConvert AI — Snowflake, 2025
11. Best AI Data Analysis Agents in 2026: 12 Platforms Compared — Tellius, 2026
12. IDC FutureScape 2026: Agent Adoption – The IT Industry’s Next Great Inflection Point — IDC, 2025

---