What is Azure Key Vault, and how do you use it from a .NET application?

Azure Key Vault

Azure Key Vault is a managed service for storing and controlling access to sensitive material, so secrets don't end up in source code, config files, or CI variables. It handles three kinds of objects:

• Secrets: arbitrary small values such as connection strings, API keys, and passwords.
• Keys: cryptographic keys (RSA, EC) used for signing, encryption, and wrapping. Operations run inside the vault, so private key material never leaves it (with HSM-backed keys, never leaves the HSM).
• Certificates: TLS/SSL certificates with lifecycle management, including auto-renewal with supported CAs.

Tiers: Standard (software-protected keys), Premium (HSM-backed keys), and Managed HSM (a dedicated, single-tenant HSM pool for the strictest compliance needs).

Key features

• Access control: Azure RBAC is the recommended model (access policies are the legacy model). Common roles are Key Vault Secrets User (read secrets), Key Vault Secrets Officer (manage them), Key Vault Crypto User, and Key Vault Administrator.
• Soft delete and purge protection: deleted items can be recovered during a retention period, and purge protection prevents permanent deletion until it ends.
• Versioning: every secret update creates a new version, so you can roll back or rotate gracefully.
• Auditing: logs go to Azure Monitor / Log Analytics.
• Network security: firewall rules and private endpoints.
• Events: Event Grid notifications for things like a secret nearing expiry.

The best practice: no credentials to reach the vault

Your app authenticates to Key Vault with a managed identity, so you don't need a secret to get your secrets. DefaultAzureCredential uses the managed identity when running in Azure, and falls back to your az login or Visual Studio sign-in locally.

1. Create the vault and grant access

az keyvault create --name my-unique-vault --resource-group my-rg \
  --location centralindia --enable-rbac-authorization true

az keyvault secret set --vault-name my-unique-vault \
  --name "ConnectionStrings--Default" --value "Server=...;Password=..."

# Enable a system-assigned identity on your App Service
az webapp identity assign --name my-unique-app-name --resource-group my-rg

# Let that identity read secrets
az role assignment create \
  --role "Key Vault Secrets User" \
  --assignee <principalId-from-previous-command> \
  --scope $(az keyvault show --name my-unique-vault --query id -o tsv)

For local development, give your own user the same role on the vault, and make sure you're signed in with az login or Visual Studio.

2. Option A: load secrets into IConfiguration (most common)

Install the packages:

dotnet add package Azure.Identity
dotnet add package Azure.Extensions.AspNetCore.Configuration.Secrets

using Azure.Identity;

var builder = WebApplication.CreateBuilder(args);

builder.Configuration.AddAzureKeyVault(
    new Uri("https://my-unique-vault.vault.azure.net/"),
    new DefaultAzureCredential());

var app = builder.Build();

// Reads the Key Vault secret "ConnectionStrings--Default"
var cs = builder.Configuration.GetConnectionString("Default");

Key Vault secret names can't contain :, so use -- instead. ConnectionStrings--Default maps to ConnectionStrings:Default. Secrets are loaded at startup, and the provider can refresh periodically if you set a ReloadInterval in AzureKeyVaultConfigurationOptions.

3. Option B: call Key Vault directly with the SDK

dotnet add package Azure.Security.KeyVault.Secrets

using Azure.Identity;
using Azure.Security.KeyVault.Secrets;

var client = new SecretClient(
    new Uri("https://my-unique-vault.vault.azure.net/"),
    new DefaultAzureCredential());

KeyVaultSecret secret = await client.GetSecretAsync("StripeApiKey");
string apiKey = secret.Value;

For keys and certificates, use Azure.Security.KeyVault.Keys (KeyClient and CryptographyClient for encrypt/sign operations) and Azure.Security.KeyVault.Certificates (CertificateClient).

Register the client once via dependency injection rather than creating it per request:

builder.Services.AddSingleton(_ => new SecretClient(
    new Uri("https://my-unique-vault.vault.azure.net/"),
    new DefaultAzureCredential()));

4. Option C: Key Vault references (no code changes)

In App Service or Azure Functions, set an app setting whose value is a reference:

@Microsoft.KeyVault(SecretUri=https://my-unique-vault.vault.azure.net/secrets/StripeApiKey/)

or

@Microsoft.KeyVault(VaultName=my-unique-vault;SecretName=StripeApiKey)

The platform resolves it using the app's managed identity and injects the value as an ordinary environment variable. Your .NET code just reads IConfiguration as usual. It's the simplest approach when you're already on App Service or Functions, and leaving the version off means the app picks up newer secret versions automatically.

Practical tips

• Cache secrets instead of fetching them on every request. Key Vault has per-vault throttling limits, and each call adds latency.
• Prefer RBAC and least privilege: Secrets User for apps, with write roles reserved for pipelines or admins.
• Use one vault per app and environment (dev/test/prod) so access can be scoped tightly.
• Rotate secrets: set expiry dates, subscribe to the SecretNearExpiry event, and have your app tolerate version changes.
• Don't log secret values, and avoid putting the vault URI in places where it matters if exposed (the URI isn't secret, but access control should be what protects it).
• Related uses: ASP.NET Core Data Protection keys can be protected with a Key Vault key, and Entra ID app credentials can be certificate-based using Key Vault certificates.

Write a query to find gaps in a sequence of numbers (missing IDs).

Finding Gaps in a Sequence (Missing IDs) in SQL

Table

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    Customer VARCHAR(100)
);

INSERT INTO Orders (OrderId, Customer) VALUES
(1, 'A'), (2, 'B'), (3, 'C'),
(6, 'D'), (7, 'E'),
(10, 'F'),
(14, 'G'), (15, 'H');

Missing IDs: 4, 5, 8, 9, 11, 12, 13.

Query 1: gap ranges (start and end of each gap)

Uses LEAD to compare each ID with the next one.

WITH NextId AS (
    SELECT
        OrderId,
        LEAD(OrderId) OVER (ORDER BY OrderId) AS NextOrderId
    FROM Orders
)
SELECT
    OrderId + 1         AS GapStart,
    NextOrderId - 1     AS GapEnd,
    NextOrderId - OrderId - 1 AS MissingCount
FROM NextId
WHERE NextOrderId - OrderId > 1
ORDER BY GapStart;

Result

GapStart	GapEnd	MissingCount
4	5	2
8	9	2
11	13	3

This is the fastest and most compact approach. It reads the table once and returns one row per gap.

Query 2: every missing ID listed individually

SQL Server / Azure SQL (recursive CTE, no numbers table needed)

WITH Bounds AS (
    SELECT MIN(OrderId) AS MinId, MAX(OrderId) AS MaxId FROM Orders
),
AllIds AS (
    SELECT MinId AS Id, MaxId FROM Bounds
    UNION ALL
    SELECT Id + 1, MaxId FROM AllIds WHERE Id < MaxId
)
SELECT a.Id AS MissingId
FROM AllIds a
LEFT JOIN Orders o ON o.OrderId = a.Id
WHERE o.OrderId IS NULL
ORDER BY a.Id
OPTION (MAXRECURSION 0);

SQL Server 2022 / Azure SQL (GENERATE_SERIES)

SELECT s.value AS MissingId
FROM GENERATE_SERIES(
        (SELECT MIN(OrderId) FROM Orders),
        (SELECT MAX(OrderId) FROM Orders)) s
LEFT JOIN Orders o ON o.OrderId = s.value
WHERE o.OrderId IS NULL
ORDER BY s.value;

PostgreSQL

SELECT g AS missing_id
FROM generate_series(
        (SELECT MIN(order_id) FROM orders),
        (SELECT MAX(order_id) FROM orders)) AS g
LEFT JOIN orders o ON o.order_id = g
WHERE o.order_id IS NULL
ORDER BY g;

Result

MissingId
4
5
8
9
11
12
13

Query 3: gaps and islands (alternative using ROW_NUMBER)

Shows the present ranges; the gaps are the spaces between them.

WITH Islands AS (
    SELECT
        MIN(OrderId) AS IslandStart,
        MAX(OrderId) AS IslandEnd
    FROM (
        SELECT OrderId,
               OrderId - ROW_NUMBER() OVER (ORDER BY OrderId) AS grp
        FROM Orders
    ) t
    GROUP BY grp
)
SELECT IslandStart, IslandEnd
FROM Islands
ORDER BY IslandStart;

Result

IslandStart	IslandEnd
1	3
6	7
10	10
14	15

Query 4: gaps without window functions (older databases)

SELECT
    o.OrderId + 1 AS GapStart,
    (SELECT MIN(o2.OrderId) FROM Orders o2 WHERE o2.OrderId > o.OrderId) - 1 AS GapEnd
FROM Orders o
WHERE NOT EXISTS (SELECT 1 FROM Orders o2 WHERE o2.OrderId = o.OrderId + 1)
  AND o.OrderId < (SELECT MAX(OrderId) FROM Orders)
ORDER BY GapStart;

Slower, since it runs subqueries per row, but it works on any database.

Query 5: gaps per group (for example, per customer or per year)

WITH NextId AS (
    SELECT
        Region,
        InvoiceNo,
        LEAD(InvoiceNo) OVER (PARTITION BY Region ORDER BY InvoiceNo) AS NextInvoiceNo
    FROM Invoices
)
SELECT Region,
       InvoiceNo + 1     AS GapStart,
       NextInvoiceNo - 1 AS GapEnd
FROM NextId
WHERE NextInvoiceNo - InvoiceNo > 1
ORDER BY Region, GapStart;

PARTITION BY restarts the check for each group, which is what you want when every group has its own numbering.

Query 6: check whether the sequence starts at 1

The queries above only find gaps between existing IDs. To catch missing IDs at the beginning:

SELECT 1 AS GapStart, MIN(OrderId) - 1 AS GapEnd
FROM Orders
HAVING MIN(OrderId) > 1;

For missing IDs after the last one, you need an expected maximum from somewhere else (a counter, a config value, or a parent table), since the table itself can't know.

Details

• LEAD approach (Query 1) is the best default: one pass, one row per gap, easy to read.
• Listing each missing ID (Query 2) needs a generated range of numbers. The recursive CTE works anywhere; GENERATE_SERIES is simpler where available (SQL Server 2022+, Azure SQL, PostgreSQL).
• Recursion limit: SQL Server stops at 100 levels by default, so use OPTION (MAXRECURSION 0) for large ranges. For millions of IDs, use a numbers (tally) table instead.
• Duplicates: if the column isn't unique, add SELECT DISTINCT in the first CTE, otherwise LEAD returns the same value and hides gaps.
• Identity columns create gaps naturally (rolled-back inserts, deletes, failed transactions), so gaps there are usually normal. Gaps matter for things like invoice numbers, where auditors expect a continuous sequence.
• Sequence start and end: decide whether leading and trailing gaps count (Query 6).

Supporting index

-- Usually the primary key already covers this. For a non-key column:
CREATE INDEX IX_Orders_OrderId ON Orders (OrderId);

An ordered index lets LEAD and ROW_NUMBER read the rows without a sort.

Compatibility

LEAD and ROW_NUMBER work in Azure SQL, SQL Server 2012+, PostgreSQL, MySQL 8.0+, Oracle, and SQLite 3.25+. GENERATE_SERIES requires SQL Server 2022+ or PostgreSQL. In MySQL, use a recursive CTE with WITH RECURSIVE for the full list of missing IDs.

Recursive query to find the full reporting chain for each employee.

Full Reporting Chain per Employee (Recursive CTE)

Table

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name       VARCHAR(100),
    ManagerId  INT NULL REFERENCES Employees(EmployeeId)
);

INSERT INTO Employees (EmployeeId, Name, ManagerId) VALUES
(1, 'Anita',  NULL),   -- CEO
(2, 'Rahul',  1),
(3, 'Priya',  1),
(4, 'Vikram', 2),
(5, 'Sneha',  2),
(6, 'Arjun',  4);

Approach

A recursive CTE has two parts:

1. Anchor member: the starting rows. Each employee paired with their direct manager.
2. Recursive member: joins the previous result to Employees to climb one level higher, repeating until it reaches a row with no manager.

Query 1: every employee with all their managers (one row per level)

WITH ReportingChain AS (
    -- Anchor: each employee and their direct manager
    SELECT
        e.EmployeeId,
        e.Name          AS EmployeeName,
        e.ManagerId     AS CurrentManagerId,
        1               AS Level
    FROM Employees e
    WHERE e.ManagerId IS NOT NULL

    UNION ALL

    -- Recursive: move up to the manager's manager
    SELECT
        rc.EmployeeId,
        rc.EmployeeName,
        m.ManagerId,
        rc.Level + 1
    FROM ReportingChain rc
    JOIN Employees m ON m.EmployeeId = rc.CurrentManagerId
    WHERE m.ManagerId IS NOT NULL
)
SELECT
    rc.EmployeeId,
    rc.EmployeeName,
    rc.Level,
    mgr.EmployeeId AS ManagerId,
    mgr.Name       AS ManagerName
FROM ReportingChain rc
JOIN Employees mgr ON mgr.EmployeeId = rc.CurrentManagerId
ORDER BY rc.EmployeeId, rc.Level;

Result (excerpt)

EmployeeName	Level	ManagerName
Arjun	1	Vikram
Arjun	2	Rahul
Arjun	3	Anita
Vikram	1	Rahul
Vikram	2	Anita
Sneha	1	Rahul
Sneha	2	Anita

Query 2: the chain as a single string per employee

WITH Chain AS (
    -- Anchor: start at every employee
    SELECT
        EmployeeId,
        Name,
        ManagerId,
        CAST(Name AS VARCHAR(1000)) AS ChainPath,
        0 AS Depth
    FROM Employees

    UNION ALL

    -- Recursive: append the next manager up
    SELECT
        c.EmployeeId,
        c.Name,
        m.ManagerId,
        CAST(c.ChainPath + ' -> ' + m.Name AS VARCHAR(1000)),
        c.Depth + 1
    FROM Chain c
    JOIN Employees m ON m.EmployeeId = c.ManagerId
)
SELECT EmployeeId, Name, ChainPath, Depth AS LevelsAboveEmployee
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY Depth DESC) AS rn
    FROM Chain
) x
WHERE rn = 1
ORDER BY EmployeeId;

Result

Name	ChainPath	LevelsAboveEmployee
Anita	Anita	0
Rahul	Rahul -> Anita	1
Priya	Priya -> Anita	1
Vikram	Vikram -> Rahul -> Anita	2
Sneha	Sneha -> Rahul -> Anita	2
Arjun	Arjun -> Vikram -> Rahul -> Anita	3

The final ROW_NUMBER() ... ORDER BY Depth DESC keeps only the longest (complete) path per employee.

Query 3: top-down hierarchy (the org tree from the CEO)

WITH OrgTree AS (
    SELECT EmployeeId, Name, ManagerId,
           0 AS Level,
           CAST(Name AS VARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.EmployeeId, e.Name, e.ManagerId,
           t.Level + 1,
           CAST(t.Path + ' > ' + e.Name AS VARCHAR(1000))
    FROM Employees e
    JOIN OrgTree t ON e.ManagerId = t.EmployeeId
)
SELECT REPLICATE('    ', Level) + Name AS OrgChart, Level, Path
FROM OrgTree
ORDER BY Path;

Result:

Anita
    Priya
    Rahul
        Sneha
        Vikram
            Arjun

Query 4: all direct and indirect reports of one manager

DECLARE @ManagerId INT = 2;

WITH Reports AS (
    SELECT EmployeeId, Name, ManagerId, 1 AS Level
    FROM Employees
    WHERE ManagerId = @ManagerId

    UNION ALL

    SELECT e.EmployeeId, e.Name, e.ManagerId, r.Level + 1
    FROM Employees e
    JOIN Reports r ON e.ManagerId = r.EmployeeId
)
SELECT * FROM Reports ORDER BY Level, Name;

Details

• UNION ALL is required between the anchor and recursive members. Use UNION only in dialects that need it for cycle handling, and expect it to be slower.
• Recursion limit: SQL Server stops at 100 levels by default. Raise or remove it with OPTION (MAXRECURSION 0) (0 = unlimited), but only after guarding against cycles.
• Cycles: if bad data makes someone their own ancestor (A manages B, B manages A), the recursion never ends. Guard with a depth cap or a path check:

-- In the recursive member:
WHERE c.ChainPath NOT LIKE '%' + m.Name + '%'   -- simple guard, or
  AND c.Depth < 20                               -- depth cap

For a precise check, track visited IDs in a delimited string ('/1/2/4/') and test CHARINDEX('/' + CAST(m.EmployeeId AS VARCHAR) + '/', c.IdPath) = 0.

• Multiple roots: if several rows have ManagerId IS NULL, Query 3 returns each as a separate tree.
• Employees with no manager (the CEO) have no rows in Query 1, but appear with depth 0 in Query 2.
• Data type: make the path column wide enough (VARCHAR(1000)). The anchor's type fixes the column type, and a too-short anchor causes truncation errors in the recursive member.

PostgreSQL / MySQL version

WITH RECURSIVE chain AS (
    SELECT employee_id, name, manager_id,
           name::text AS chain_path,
           0 AS depth
    FROM employees

    UNION ALL

    SELECT c.employee_id, c.name, m.manager_id,
           c.chain_path || ' -> ' || m.name,
           c.depth + 1
    FROM chain c
    JOIN employees m ON m.employee_id = c.manager_id
)
SELECT DISTINCT ON (employee_id) employee_id, name, chain_path, depth
FROM chain
ORDER BY employee_id, depth DESC;

Notes: the keyword is WITH RECURSIVE. Use CONCAT() instead of || in MySQL, and replace DISTINCT ON with the ROW_NUMBER() pattern from Query 2. Oracle supports both recursive CTEs and CONNECT BY PRIOR.

Supporting index

CREATE INDEX IX_Employees_ManagerId ON Employees (ManagerId) INCLUDE (Name);

Each recursive step joins on ManagerId or EmployeeId. The primary key covers the second, and this index covers the first.

Alternative: hierarchyid (SQL Server)

For large, read-heavy hierarchies, the hierarchyid type stores the path in the column and avoids recursion at query time:

ALTER TABLE Employees ADD OrgNode hierarchyid;
-- Ancestors of an employee:
SELECT * FROM Employees e
WHERE @targetNode.IsDescendantOf(e.OrgNode) = 1;

It requires maintaining the node values on every insert or reparent, so use it when hierarchy reads far outnumber writes.

What is Azure Service Bus, and how does it work?

Azure Service Bus

Azure Service Bus is a fully managed enterprise message broker. It sits between applications and lets them communicate asynchronously: a sender puts a message on the broker and moves on, and a receiver picks it up whenever it's ready. This decouples services in time (they don't need to be online together), in load (a burst of messages is buffered), and in technology (any client that speaks AMQP, or uses the SDKs, can participate).

Core concepts

• Namespace: the container for your messaging entities, with its own endpoint, pricing tier, and network and security settings.
• Queue: point-to-point messaging. Many senders can send, but each message is delivered to exactly one receiver (competing consumers share the load).
• Topic and subscriptions: publish/subscribe. A sender publishes to a topic, and each subscription gets its own copy of every matching message. Each subscription behaves like a virtual queue.
• Rules and filters: on a subscription, you can filter messages by properties (e.g., region = 'EU') so each subscriber only receives what it cares about.
• Message: a body plus system properties (MessageId, SessionId, TimeToLive, etc.) and custom application properties.

How it works

1. Send: the producer sends a message to a queue or topic. The broker stores it durably (replicated storage) before acknowledging.
2. Store: the message waits until a consumer retrieves it or its time-to-live expires.
3. Receive: the consumer receives the message using one of two modes:
   • Peek-lock (default): the message is locked, not deleted. The consumer processes it, then completes it (removed). If processing fails, it can abandon it (becomes available again) or dead-letter it. If the lock expires without a response, the message returns to the queue.
   • Receive-and-delete: the message is removed immediately on receipt. Faster, but it's lost if the consumer crashes mid-processing.
4. Retry and dead-letter: each message has a delivery count. Once it exceeds MaxDeliveryCount (default 10), it moves to the dead-letter queue (DLQ), a sub-queue where you can inspect and reprocess poison messages.

Key features

• Delivery guarantees: at-least-once by default, so consumers should be idempotent. Duplicate detection can discard resends within a time window.
• Sessions: group related messages with a SessionId to get FIFO ordering within the group and exclusive processing by one consumer at a time.
• Scheduled messages: enqueue now, deliver at a future time.
• Transactions: group operations (e.g., complete one message and send another) atomically.
• Auto-forwarding: chain queues or subscriptions together.
• Security: Microsoft Entra ID with RBAC (preferred over shared access keys), private endpoints, and customer-managed keys.
• Resilience: availability zones, plus Geo-Replication or Geo-Disaster Recovery on the Premium tier.

Tiers

• Standard: pay-as-you-go, shared infrastructure, messages up to 256 KB.
• Premium: dedicated resources (messaging units), predictable performance, VNet and private endpoint support, and larger messages (up to 100 MB).

Quick C# example

await using var client = new ServiceBusClient(
    "mynamespace.servicebus.windows.net", new DefaultAzureCredential());

// Send
ServiceBusSender sender = client.CreateSender("orders");
await sender.SendMessageAsync(new ServiceBusMessage("Order #1234 placed"));

// Receive
ServiceBusReceiver receiver = client.CreateReceiver("orders");
ServiceBusReceivedMessage msg = await receiver.ReceiveMessageAsync();
Console.WriteLine(msg.Body.ToString());
await receiver.CompleteMessageAsync(msg);

For real workloads, ServiceBusProcessor handles concurrency, lock renewal, and error handling for you, and Azure Functions has a Service Bus trigger that does the same with almost no code.

When to use it

• Order processing, payments, and other workflows where messages must not be lost
• Decoupling microservices and smoothing out traffic spikes (load leveling)
• Fan-out of events to multiple downstream systems via topics
• Ordered processing per customer or entity using sessions

Service Bus vs. related services

	Service Bus	Storage Queues	Event Grid	Event Hubs
Model	Enterprise broker (queues, topics)	Simple queue	Event routing (reactive)	High-throughput event streaming
Ordering, sessions, transactions	Yes	No	No	Ordering per partition
Best for	Business messages and commands	Basic, cheap queuing	"Something happened" notifications	Telemetry and log ingestion

Rule of thumb: use Service Bus when a message represents work that must be processed reliably, Event Grid for lightweight notifications, and Event Hubs for massive streams of telemetry.

Find the longest consecutive streak of daily logins for each user.

Longest Consecutive Daily Login Streak per User (SQL)

Table

CREATE TABLE UserLogins (
    UserId    INT,
    LoginTime DATETIME2
);

Sample data:

INSERT INTO UserLogins (UserId, LoginTime) VALUES
(1, '2026-10-01 08:15'), (1, '2026-10-01 19:40'),
(1, '2026-10-02 09:00'), (1, '2026-10-03 10:30'),
(1, '2026-10-06 07:45'), (1, '2026-10-07 08:05'),
(2, '2026-10-01 12:00'), (2, '2026-10-03 12:00'), (2, '2026-10-04 12:00'),
(3, '2026-10-05 22:10');

Approach: gaps and islands

1. Reduce logins to one row per user per day (users can log in many times a day).
2. Number each user's days with ROW_NUMBER().
3. Subtract that number from the date. For consecutive days the result is constant, so it identifies the streak ("island").
4. Group by that value, count days per streak, and take the maximum per user.

Query (SQL Server / Azure SQL)

WITH DistinctDays AS (
    SELECT DISTINCT
        UserId,
        CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Numbered AS (
    SELECT
        UserId,
        LoginDate,
        DATEADD(DAY,
                -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate),
                LoginDate) AS StreakKey
    FROM DistinctDays
),
Streaks AS (
    SELECT
        UserId,
        MIN(LoginDate) AS StreakStart,
        MAX(LoginDate) AS StreakEnd,
        COUNT(*)       AS StreakDays
    FROM Numbered
    GROUP BY UserId, StreakKey
),
Ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY UserId
               ORDER BY StreakDays DESC, StreakStart DESC
           ) AS rn
    FROM Streaks
)
SELECT UserId, StreakStart, StreakEnd, StreakDays AS LongestStreak
FROM Ranked
WHERE rn = 1
ORDER BY UserId;

Result

UserId	StreakStart	StreakEnd	LongestStreak
1	2026-10-01	2026-10-03	3
2	2026-10-03	2026-10-04	2
3	2026-10-05	2026-10-05	1

How the streak key works

For user 1, after de-duplicating days:

LoginDate	ROW_NUMBER	LoginDate - ROW_NUMBER (StreakKey)
2026-10-01	1	2026-09-30
2026-10-02	2	2026-09-30
2026-10-03	3	2026-09-30
2026-10-06	4	2026-10-02
2026-10-07	5	2026-10-02

Consecutive days share the same key. A gap changes it, starting a new island.

Details

• De-duplicate first. Without DISTINCT on the date, multiple logins in a day break the row numbering and the streak math.
• CAST(... AS DATE) removes the time portion. If your timestamps are stored in UTC and "day" means the user's local day, convert the time zone first (AT TIME ZONE).
• Ties: if a user has two streaks of equal length, the query returns the most recent one (StreakStart DESC). Change the order to ASC for the earliest, or use RANK() to return all tied streaks.
• Users with no logins don't appear. Left join from a Users table if you need them with a 0.
• Only the length, not the dates: replace the last two CTEs with

SELECT UserId, MAX(StreakDays) AS LongestStreak
FROM Streaks
GROUP BY UserId;

Variation: current streak (ending today)

WITH DistinctDays AS (
    SELECT DISTINCT UserId, CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Numbered AS (
    SELECT UserId, LoginDate,
           DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginDate), LoginDate) AS StreakKey
    FROM DistinctDays
),
Streaks AS (
    SELECT UserId, MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS StreakDays
    FROM Numbered
    GROUP BY UserId, StreakKey
)
SELECT UserId, StreakStart, StreakEnd, StreakDays AS CurrentStreak
FROM Streaks
WHERE StreakEnd >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE));  -- logged in today or yesterday

Alternative: LAG-based

WITH DistinctDays AS (
    SELECT DISTINCT UserId, CAST(LoginTime AS DATE) AS LoginDate
    FROM UserLogins
),
Flagged AS (
    SELECT UserId, LoginDate,
           CASE WHEN DATEDIFF(DAY,
                    LAG(LoginDate) OVER (PARTITION BY UserId ORDER BY LoginDate),
                    LoginDate) = 1
                THEN 0 ELSE 1 END AS IsNewStreak
    FROM DistinctDays
),
Grouped AS (
    SELECT UserId, LoginDate,
           SUM(IsNewStreak) OVER (
               PARTITION BY UserId ORDER BY LoginDate
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
           ) AS StreakId
    FROM Flagged
)
SELECT UserId, StreakId,
       MIN(LoginDate) AS StreakStart, MAX(LoginDate) AS StreakEnd, COUNT(*) AS StreakDays
FROM Grouped
GROUP BY UserId, StreakId;

This flags a new streak whenever the previous login day isn't exactly one day earlier, then a running sum assigns a streak ID. It's easier to read and extends to other rules (for example, allowing a one-day gap).

PostgreSQL version

WITH days AS (
    SELECT DISTINCT user_id, login_time::date AS login_date
    FROM user_logins
),
numbered AS (
    SELECT user_id, login_date,
           login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS streak_key
    FROM days
),
streaks AS (
    SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_days
    FROM numbered
    GROUP BY user_id, streak_key
)
SELECT DISTINCT ON (user_id) user_id, streak_start, streak_end, streak_days
FROM streaks
ORDER BY user_id, streak_days DESC, streak_start DESC;

Supporting index

CREATE INDEX IX_UserLogins_User_Time
ON UserLogins (UserId, LoginTime);

This lets the database read each user's logins already ordered. For very large tables, consider a pre-aggregated daily table (UserId, LoginDate) so the DISTINCT step isn't repeated on every run.

Compatibility

Window functions (ROW_NUMBER, LAG) work in Azure SQL, SQL Server 2012+, PostgreSQL, MySQL 8.0+, Oracle, and SQLite 3.25+. Only the date arithmetic changes by dialect: DATEADD / DATEDIFF in SQL Server, date subtraction in PostgreSQL, DATE_SUB / DATEDIFF in MySQL.

Write a query to rank employees based on salary.

Employee Salary Ranking

Here is the complete, production-ready SQL script. It sets up the table structure, populates it with realistic sample data (including identical salaries to demonstrate tie-handling), and runs the ranking query.

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

-- 2. INSERT SAMPLE DATA
-- Includes identical salaries in 'Sales' and 'Engineering' to showcase tie-handling
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Charlie Brown', 'Engineering', 95000.00),
(4, 'Diana Prince', 'Marketing', 82000.00),
(5, 'Evan Wright', 'Sales', 75000.00),
(6, 'Fiona Gallagher', 'Engineering', 110000.00),
(7, 'George Clark', 'Sales', 75000.00),
(8, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE RANKING QUERY
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Unique row index per record
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    
    -- Traditional rank (Skips ranks after ties: 1, 2, 2, 2, 5...)
    RANK() OVER (ORDER BY salary DESC) AS standard_rank,
    
    -- Dense rank (No gaps in ranking order: 1, 2, 2, 2, 3...)
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Use code with caution.

Expected Query Output

employee_id	employee_name	department	salary	row_num	standard_rank	dense_rank
6	Fiona Gallagher	Engineering	110000.00	1	1	1
1	Alice Smith	Engineering	95000.00	2	2	2
3	Charlie Brown	Engineering	95000.00	3	2	2
4	Diana Prince	Marketing	82000.00	4	4	3
2	Bob Jones	Sales	75000.00	5	5	4
5	Evan Wright	Sales	75000.00	6	5	4
7	George Clark	Sales	75000.00	7	5	4
8	Hannah Abbott	Marketing	68000.00	8	8	5

Key Takeaways from the Data Visual

• Alice and Charlie both make $95,000.00. RANK places them both at 2, and skips 3. DENSE_RANK places them at 2, but the next person down (Diana) gets 3.
• Bob, Evan, and George all make $75,000.00. Notice how RANK skips all the way from 5 to 8 for the final row, while DENSE_RANK moves perfectly from 4 to 5.

Running total of salaries by department.

Running Total of Salaries by Department in SQL: A Complete Guide

How to build cumulative totals with window functions, avoid the common pitfalls, and keep the query fast

Introduction

A cumulative number shows up in almost every reporting request: sales to date, headcount growth, or, in this guide, the running total of salaries within each department. A running total shows how a value accumulates row by row, and it resets whenever the group changes.

Before window functions, this meant a self-join or a correlated subquery, both slow and hard to read. Today a single SUM() OVER (...) does the job. This post walks through the full solution, explains each clause, covers the mistakes that trip people up, and finishes with performance tuning.

The Scenario

We have an Employees table and want to see, for every employee, how much the department's payroll has accumulated as of their hire date.

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name       VARCHAR(100),
    Department VARCHAR(50),
    Salary     DECIMAL(10,2),
    HireDate   DATE
);

Sample data:

INSERT INTO Employees (EmployeeId, Name, Department, Salary, HireDate) VALUES
(1, 'Asha',  'Engineering', 90000, '2020-01-10'),
(2, 'Ravi',  'Engineering', 85000, '2021-03-05'),
(3, 'Meera', 'Engineering', 95000, '2022-07-19'),
(4, 'Karan', 'Sales',       60000, '2019-11-02'),
(5, 'Neha',  'Sales',       65000, '2021-06-14');

The Solution

SELECT
    Department,
    EmployeeId,
    Name,
    HireDate,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY HireDate, EmployeeId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Employees
ORDER BY Department, HireDate, EmployeeId;

Result:

Department	Name	HireDate	Salary	RunningTotal
Engineering	Asha	2020-01-10	90,000	90,000
Engineering	Ravi	2021-03-05	85,000	175,000
Engineering	Meera	2022-07-19	95,000	270,000
Sales	Karan	2019-11-02	60,000	60,000
Sales	Neha	2021-06-14	65,000	125,000

Notice that Sales starts again at 60,000 rather than continuing from Engineering's total. That reset is the point of partitioning.

How the Query Works

The OVER clause has three parts, and each one controls a different aspect of the calculation.

1. PARTITION BY Department

This splits the rows into independent groups, one per department. The sum is calculated inside each group, so totals never leak from one department into another. Unlike GROUP BY, partitioning does not collapse rows. Every employee row stays in the output.

2. ORDER BY HireDate, EmployeeId

A running total only means something with an order. Here we accumulate in hire-date order. EmployeeId is added as a tie-breaker: if two employees share a hire date, the database would otherwise be free to order them arbitrarily, and the running totals could change between executions.

3. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This is the frame. It tells the database which rows, relative to the current one, feed the sum. Here it means "from the first row of the partition through the current row." That is exactly the definition of a running total.

The Common Gotcha: Always Specify ROWS

You can write the query without the frame:

SUM(Salary) OVER (PARTITION BY Department ORDER BY HireDate)

It looks equivalent, but it isn't. Without a frame clause, SQL uses RANGE UNBOUNDED PRECEDING by default. RANGE treats rows with equal ORDER BY values as peers, so if two employees were hired on the same day, both rows receive the same total, which is usually not the intent. In SQL Server, RANGE is also typically slower because it can spill to disk.

Writing ROWS explicitly fixes both problems and makes your intent obvious to the next reader.

Variation 1: Monthly Department Payroll

Often you want the cumulative payroll over time rather than per employee. Aggregate first with GROUP BY, then apply the window function on top of the result:

WITH Monthly AS (
    SELECT Department,
           DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1) AS Month,
           SUM(Salary) AS MonthlySalary
    FROM Employees
    GROUP BY Department, DATEFROMPARTS(YEAR(HireDate), MONTH(HireDate), 1)
)
SELECT Department, Month, MonthlySalary,
       SUM(MonthlySalary) OVER (
           PARTITION BY Department
           ORDER BY Month
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS RunningTotal
FROM Monthly
ORDER BY Department, Month;

The CTE (WITH Monthly AS ...) produces one row per department per month. The outer query then accumulates those monthly figures. This two-step pattern, aggregate then window, is useful any time you need a running total over time periods.

Variation 2: Running Total Beside the Department Total

You can use several windows in the same query. This version shows the running total and the department's overall total on every row:

SELECT Department, Name, Salary,
       SUM(Salary) OVER (
           PARTITION BY Department
           ORDER BY Salary DESC, EmployeeId
           ROWS UNBOUNDED PRECEDING
       ) AS RunningTotal,
       SUM(Salary) OVER (PARTITION BY Department) AS DepartmentTotal
FROM Employees;

The second SUM has no ORDER BY, so it covers the whole partition and returns the same total on every row of the department. From here, the share of the department payroll reached so far is simply:

RunningTotal * 100.0 / DepartmentTotal

Ordering by Salary DESC makes this a "top earners first" view, which is handy for seeing how much of the payroll the highest-paid people account for.

Variation 3: Without Window Functions

On very old databases that lack window functions, a correlated subquery gives the same result:

SELECT e.Department, e.Name, e.Salary,
       (SELECT SUM(e2.Salary)
        FROM Employees e2
        WHERE e2.Department = e.Department
          AND (e2.HireDate < e.HireDate
               OR (e2.HireDate = e.HireDate AND e2.EmployeeId <= e.EmployeeId))
       ) AS RunningTotal
FROM Employees e;

It works, but it re-scans the table for every output row, so cost grows quickly with data size. The window version reads the data once, which is why it is the preferred approach wherever it's available.

Performance Tuning

An index that matches the PARTITION BY and ORDER BY columns lets the database read rows already in the right order and skip a separate sort:

CREATE INDEX IX_Employees_Dept_Hire
ON Employees (Department, HireDate, EmployeeId)
INCLUDE (Salary);

The key columns follow the partition and sort order, and INCLUDE (Salary) makes the index covering, so the query never has to visit the base table.

Other tips:

• Filter early. Apply WHERE conditions before the window is computed so fewer rows are processed.
• Read the execution plan. A Sort or Spool operator usually means the index doesn't match the window's partition and order columns.
• Select only what you need. Wide result sets make the window operator more expensive.

Database Compatibility

This syntax works in:

Database	Support
Azure SQL Database / SQL Server	2012 and later
PostgreSQL	8.4 and later
MySQL	8.0 and later
Oracle	Yes
SQLite	3.25 and later

The only dialect-specific part of the examples is DATEFROMPARTS. In PostgreSQL, replace it with DATE_TRUNC('month', HireDate).

Key Takeaways

1. Use SUM() OVER (PARTITION BY ... ORDER BY ...) for running totals instead of self-joins or subqueries.
2. PARTITION BY resets the total per group, and unlike GROUP BY it keeps every row.
3. Add a unique tie-breaker column to the ORDER BY for deterministic results.
4. Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to avoid tie-related surprises and the slower RANGE default.
5. To get cumulative values over time, aggregate first in a CTE, then apply the window.
6. Back the query with an index on the partition and order columns, including the summed column.