Write a query to calculate the difference between current row and previous row's salary (lag function).

To calculate the difference between the current row's salary and the previous row's salary, you use the LAG() window function. LAG() allows you to look backward at a previous row's data within your result set without executing a complex self-join.

The Query

Here is the standard SQL query. It orders the employees by salary (highest to lowest) and computes the exact structural difference between adjacent positions:

sql

SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    -- Step 1: Fetch the previous row's salary
    LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS previous_salary,
    
    -- Step 2: Subtract the previous row's salary from the current row's salary
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_difference
FROM employees;

Use code with caution.

Syntax Deep Dive: LAG(column, offset, default)

• salary: The target column you want to retrieve a historical value from.
• 1 (Offset): How many rows to look backward. A value of 1 targets the immediate previous row.
• 0 or salary (Default Value): What to return if there is no previous row (e.g., the very first row in the dataset).
  • Passing 0 or omitting it helps show a blank baseline.
  • Passing the column itself (salary) ensures that subtracting it from the current row results in a clean 0 instead of a breaking NULL value on row one.

---

Complete Playground Script (Schema + Test Data)

Copy and execute this script to test the calculations live:

sql

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

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice Smith', 'Engineering', 95000.00),
(2, 'Bob Jones', 'Sales', 75000.00),
(3, 'Diana Prince', 'Marketing', 82000.00),
(4, 'Fiona Gallagher', 'Engineering', 110000.00),
(5, 'Hannah Abbott', 'Marketing', 68000.00);

-- 3. EXECUTE LAG QUERY
SELECT 
    employee_name,
    department,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_employee_salary,
    salary - LAG(salary, 1, salary) OVER (ORDER BY salary DESC) AS salary_diff_from_previous
FROM employees;

Use code with caution.

Expected Query Output

employee_name	department	salary	previous_employee_salary	salary_diff_from_previous
Fiona Gallagher	Engineering	110000.00	NULL	0.00
Alice Smith	Engineering	95000.00	110000.00	-15000.00
Diana Prince	Marketing	82000.00	95000.00	-13000.00
Bob Jones	Sales	75000.00	82000.00	-7000.00
Hannah Abbott	Marketing	68000.00	75000.00	-7000.00

(Note: Fiona displays 0.00 difference because she is at the top of the chain; there is no previous record to subtract from her total.)

What is the difference between a Service Bus Queue and a Topic?

Service Bus Queue vs. Topic

The core difference is the delivery model: a queue delivers each message to exactly one consumer, while a topic delivers a copy of each message to every interested subscriber.

Queue: point-to-point

Senders put messages on the queue, and receivers pull from it. If several receivers listen to the same queue, they are competing consumers: each message goes to only one of them, so adding receivers spreads the load.

Sender(s) ──► [ Queue ] ──► Receiver A  (or B, or C; one gets each message)

Topic: publish/subscribe

Senders publish to a topic, but receivers don't read from the topic itself. They read from subscriptions, each of which acts like its own virtual queue. Every subscription receives its own copy of each message that matches its filter.

                      ┌─► [ Subscription: billing ]   ──► Billing service
Sender ──► [ Topic ] ─┼─► [ Subscription: shipping ]  ──► Shipping service
                      └─► [ Subscription: analytics ] ──► Analytics service

Within a single subscription, multiple consumers still compete, so you can scale out each subscriber independently.

Side-by-side

	Queue	Topic + subscriptions
Pattern	Point-to-point	Publish/subscribe (one-to-many)
Copies per message	One, consumed once	One per matching subscription
Receivers read from	The queue	A subscription (not the topic)
Sender knows the receivers?	Effectively one logical consumer	No, the sender is unaware of subscribers
Filtering	None	Rules and filters per subscription (SQL or correlation filters, with optional actions)
Adding a new consumer	Competes with existing ones for messages	Create a new subscription; existing ones are unaffected
Dead-letter queue	One DLQ	One DLQ per subscription
Tiers	Standard and Premium	Standard and Premium

Filters on subscriptions

Topics get much of their power from subscription rules:

// Only receive orders from the EU
await adminClient.CreateSubscriptionAsync(
    new CreateSubscriptionOptions("orders", "eu-orders"),
    new CreateRuleOptions("EuOnly", new SqlRuleFilter("region = 'EU'")));

A subscription with no rule gets everything by default. Note that a subscription's default rule is "match all", so if you add your own filter, remove the default rule or it will still match every message.

Sending and receiving in code

The code is nearly identical. The sender doesn't care which it targets, and only the receiver changes:

// Send (queue name or topic name)
var sender = client.CreateSender("orders");
await sender.SendMessageAsync(new ServiceBusMessage("Order #1234 placed"));

// Receive from a queue
var queueReceiver = client.CreateReceiver("orders");

// Receive from a topic subscription
var subReceiver = client.CreateReceiver("orders", "billing");

Because sending code is the same, you can start with a queue and move to a topic later without changing producers (you'd recreate the entity, though, as a queue can't be converted into a topic).

When to use which

Use a queue when:

• One service should process each message (work distribution, task processing).
• You want load leveling or competing consumers for throughput.
• The sender and receiver are tightly paired, e.g. "process this payment."

Use a topic when:

• Multiple independent systems must react to the same event (an "order placed" event consumed by billing, shipping, and notifications).
• Different consumers want different subsets of messages (via filters).
• You expect to add new consumers over time without touching the publisher.

Shared behavior

Queues and subscriptions share the same features: peek-lock, dead-lettering, sessions, scheduled messages, duplicate detection (configured at the topic level for topics), and TTL. A subscription is, in effect, a queue fed by a topic.

Rule of thumb

• Command ("do this work") → queue
• Event ("this happened") → topic

If you have a single consumer today but may add more, a topic with one subscription is a cheap way to keep your options open.

Conventional vs. attribute routing?

With conventional routing, you map out all your URL patterns in one central route table. Attribute routing, on the other hand, lets you drop those rules directly onto the controller and action itself. The examples here use older tech like ASP.NET MVC 5 and Web API 2, but don't worry—the exact same concept applies to ASP.NET Core.

Conventional routing:-

Patterns are registered once at startup file and apply to many controllers by naming convention.

public static void RegisterRoutes(RouteCollection routes)
{
    routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

    routes.MapRoute(
        name: "Default",
        url: "{controller}/{action}/{id}",
        defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional });
}

/Products/Details/5 goes to ProductsController.Details(5). Add a new controller and its actions follow the same pattern with no extra routing code.

Attribute routing:-

The URL is declared next to the code that handles it.

// RouteConfig: enable it BEFORE the conventional routes
routes.MapMvcAttributeRoutes();
// Web API 2: config.MapHttpAttributeRoutes();

[RoutePrefix("orders")]
public class OrdersController : Controller
{
    [Route("")]                              // /orders
    public ActionResult Index() { return View(); }

    [Route("{id:int}")]                      // /orders/42
    public ActionResult Details(int id) { return View(); }

    [Route("{id:int}/items/{itemId:int}")]   // /orders/42/items/7
    public ActionResult Item(int id, int itemId) { return View(); }
}

Differences:-

	Conventional	Attribute
Where defined	Central route table (RouteConfig)	On controllers and actions
Style	One pattern serves many controllers	One URL per action, explicit
URL flexibility	Best for uniform shapes like {controller}/{action}/{id}	Handles irregular, nested, or custom URLs easily
Readability	You must check the route table to know a URL	The URL is visible right on the action
Maintenance	Change one rule to affect many actions	Edit each action, but changes stay local
Refactoring risk	Renaming a controller or action changes its URL	URLs stay stable when you rename methods
Constraints	Defined in the route's constraints object	Inline, such as {id:int}, {name:alpha}
Order	First match in the table wins, so order matters	Rarely order-sensitive, but ambiguity still errors
Best for	Classic MVC sites with page-like URLs	REST APIs, nested resources, SEO-friendly URLs

Why attribute routing is often preferred for APIs

• Nested resources such as /customers/{customerId}/orders/{orderId} are awkward to express with a global pattern.
• Stable public URLs. Renaming GetOrder to FetchOrder does not change /orders/{id}.
• Optional parameters and defaults can be written inline: [Route("archive/{year:int?}")].
• Route names can be set for URL generation: [Route("{id}", Name = "OrderById")].
• Multiple URLs for one action are possible by stacking attributes.

When conventional routing still fits

• A traditional content site where nearly every page follows {controller}/{action}/{id}.
• Many controllers that should behave uniformly, with no need to repeat a route on each one.
• A default homepage and catch-all fallbacks in a single place.

You can mix both

A common setup uses attribute routes for specific or API-style URLs and a conventional default route for everything else. Register attribute routes first so they take priority:

routes.MapMvcAttributeRoutes();   // specific routes first

routes.MapRoute(                  // general fallback last
    name: "Default",
    url: "{controller}/{action}/{id}",
    defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional });

Common mistakes

• Forgetting MapMvcAttributeRoutes(). The [Route] attributes are silently ignored.
• Calling it after the conventional route. The general route can catch requests first.
• Two actions matching the same URL and verb. This throws an "ambiguous match" error. Use constraints or different templates.
• Missing [RoutePrefix] or typos in prefixes. URLs end up different from what you intended.
• Mixing with Web API. Web API has its own route table, so MapHttpAttributeRoutes() is separate from MapMvcAttributeRoutes().

In ASP.NET Core

The same two ideas exist. Conventional routing uses MapControllerRoute, and attribute routing uses [Route], [HttpGet("...")], and similar attributes. One key difference is that in Core, an action that has route attributes is not reachable through conventional routes. Each action uses one style or the other.

Which one to choose?

• Building an API? Use attribute routing.
• Classic MVC site with uniform URLs? Conventional routing is fine and less repetitive.
• Need both? Use attribute routes for the special cases and a conventional default as the fallback.

Write a query to perform a conditional aggregation (count males and females in each department).

To perform conditional aggregation in SQL, you combine the SUM() or COUNT() aggregate functions with a CASE WHEN statement. This allows you to inspect row attributes individually and increment separate totals within the same GROUP BY execution.

The Query

Assuming your table is named employees and contains columns for department and gender, here is the standard, optimized SQL query:

sql

SELECT 
    department,
    -- Method 1: SUM with CASE WHEN (Standard across all SQL flavors)
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count,
    
    -- Method 2: COUNT with CASE WHEN (Alternative syntax)
    COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS alt_male_count,
    COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS alt_female_count
FROM employees
GROUP BY department
ORDER BY department;

Use code with caution.

How It Works

• The CASE Evaluator: For every row processed within a department, the CASE statement inspects the value of the gender column.
• SUM(CASE ... THEN 1 ELSE 0): If the condition matches, it outputs a 1. If it doesn't match, it outputs a 0. The SUM() function then tallies up these numbers, effectively calculation a count.
• COUNT(CASE ... THEN 1 END): The COUNT() function ignores NULL values. If the condition matches, it returns a 1. If it doesn't match, it implicitly returns NULL (since there is no ELSE clause), so it isn't counted.

---

Complete Playground Script (Schema + Test Data)

You can use this complete script to build the schema, load data, and instantly verify the execution logic:

sql

-- 1. CREATE TABLE SCHEMA
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    gender VARCHAR(10)
);

-- 2. INSERT SAMPLE DATA
INSERT INTO employees (employee_id, employee_name, department, gender) VALUES
(1, 'Alice', 'Engineering', 'Female'),
(2, 'Bob', 'Sales', 'Male'),
(3, 'Charlie', 'Engineering', 'Male'),
(4, 'Diana', 'Engineering', 'Female'),
(5, 'Evan', 'Sales', 'Male'),
(6, 'Fiona', 'Marketing', 'Female'),
(7, 'George', 'Sales', 'Male'),
(8, 'Hannah', 'Marketing', 'Female');

-- 3. EXECUTE CONDITIONAL AGGREGATION
SELECT 
    department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department
ORDER BY department;

Use code with caution.

Expected Query Output

department	male_count	female_count
Engineering	1	2
Marketing	0	2
Sales	3	0