Querying with SQL | Guided by A Guide to Cloud

SQL in Fabric: Two ways to query

Simple explanation

Think of two ways to order food.

The Visual Query Editor is like ordering from a menu with pictures — you point and click to choose what you want. The SQL query editor is like writing a custom order in the kitchen’s language — more flexible but requires knowing the syntax.

Both produce the same result. The Visual Query Editor is great for exploring data quickly. SQL is essential for complex queries, stored procedures, and automation.

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

Exam tip: Visual Query Editor generates T-SQL

The exam may ask what happens behind the scenes when you use the Visual Query Editor. Answer: it generates standard T-SQL. You can copy this SQL into notebooks, stored procedures, or external tools. The Visual Query Editor is a productivity tool, not a separate query engine.

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

Function	What It Does	Example
`ROW_NUMBER()`	Sequential number per partition	Rank products 1, 2, 3 within each category
`RANK()`	Rank with gaps for ties	1, 2, 2, 4 (skips 3)
`DENSE_RANK()`	Rank without gaps	1, 2, 2, 3 (no skip)
`SUM() OVER`	Running or cumulative sum	Cumulative revenue by month
`AVG() OVER`	Moving average	7-day rolling average
`LAG()` / `LEAD()`	Previous / next row value	Compare today’s sales to yesterday

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;

Scenario: James builds client comparison queries

James at Summit Consulting uses window functions to compare client performance against benchmarks. For each client, he calculates their percentile rank across the portfolio:

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

A client at percentile 0.85 performs better than 85% of Summit’s client base. James uses this to identify which clients need attention and which are exemplary.

Question

What does the Visual Query Editor do in Fabric?

Click or press Enter to reveal answer

Answer

A drag-and-drop query builder that lets you select columns, filter, join, and aggregate data without writing SQL. It generates T-SQL behind the scenes — you can view and edit the generated code. Available in warehouses and lakehouse SQL endpoints.

Click to flip back

Question

What is the difference between RANK() and DENSE_RANK()?

Click or press Enter to reveal answer

Answer

Both assign ranks based on ORDER BY. RANK() leaves gaps after ties (1, 2, 2, 4). DENSE_RANK() has no gaps (1, 2, 2, 3). Use RANK when position matters; use DENSE_RANK when consecutive numbering matters.

Click to flip back

Question

What is a CTE (Common Table Expression)?

Click or press Enter to reveal answer

Answer

A named temporary result set defined with the WITH clause. CTEs make complex queries readable by breaking them into logical steps. They exist only for the duration of the query — they are not stored objects.

Click to flip back

Knowledge Check

James at Summit Consulting needs to find the top 3 products by revenue in each region. Which SQL approach is most appropriate?

Knowledge Check

Anita at FreshCart wants to compare each store's daily revenue to the previous day. Which SQL function should she use?

🎬 Video coming soon

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