Querying with SQL

SQL in Fabric: Two ways to query

Visual Query Editor

The Visual Query Editor lets you build queries without writing SQL:

Capabilities

• Drag tables onto the canvas
• Select columns by checking/unchecking
• Filter with visual controls (equals, contains, between, etc.)
• Aggregate with group-by and summary operations
• Join tables by drawing connections
• Sort and limit results
• View the generated SQL — switch to SQL view at any time

When to use it

• Quick data exploration and profiling
• Building queries when you are learning the schema
• Non-technical users who need ad-hoc analysis
• Generating SQL that you can then refine manually

SQL querying essentials

SELECT, filter, aggregate

-- Basic: revenue by region for 2026
SELECT
    s.region,
    SUM(f.total_amount) AS total_revenue,
    COUNT(DISTINCT f.sale_id) AS transaction_count,
    AVG(f.total_amount) AS avg_transaction
FROM dbo.fact_sales f
JOIN dbo.dim_store s ON f.store_key = s.store_key
JOIN dbo.dim_date d ON f.date_key = d.date_key
WHERE d.year = 2026
GROUP BY s.region
HAVING SUM(f.total_amount) > 100000
ORDER BY total_revenue DESC;

Window functions

Window functions are heavily tested — they compute values across a set of rows related to the current row.

-- Running total of daily revenue per store
SELECT
    date_key,
    store_key,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY store_key
        ORDER BY date_key
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM dbo.agg_daily_store_sales;

-- Rank stores by revenue within each region
SELECT
    region,
    store_name,
    total_revenue,
    RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS rank_in_region
FROM dbo.vw_store_revenue;

Common window functions

CTEs (Common Table Expressions)

CTEs make complex queries readable:

-- Find stores performing below regional average
WITH regional_avg AS (
    SELECT
        s.region,
        AVG(f.total_amount) AS avg_revenue
    FROM dbo.fact_sales f
    JOIN dbo.dim_store s ON f.store_key = s.store_key
    GROUP BY s.region
),
store_revenue AS (
    SELECT
        s.store_name,
        s.region,
        SUM(f.total_amount) AS store_revenue
    FROM dbo.fact_sales f
    JOIN dbo.dim_store s ON f.store_key = s.store_key
    GROUP BY s.store_name, s.region
)
SELECT
    sr.store_name,
    sr.region,
    sr.store_revenue,
    ra.avg_revenue AS regional_average
FROM store_revenue sr
JOIN regional_avg ra ON sr.region = ra.region
WHERE sr.store_revenue < ra.avg_revenue
ORDER BY sr.store_revenue;

SELECT client_name, annual_revenue,
    PERCENT_RANK() OVER (ORDER BY annual_revenue) AS percentile
FROM client_summary;

Next up: Querying with KQL — Kusto Query Language for time-series analytics and Eventhouse data.