---
title: "Mastering SQL Date Extraction and Monthly Trends Using LAG()"
description: "Learn how to extract the year from a date in MySQL and SQL Server, and analyze monthly customer order growth using the SQL LAG() window function. Includes sample tables, inserts, and output."
url: https://srinimf.com/2025/10/14/mastering-sql-date-extraction-and-monthly-trends-using-lag/
date: 2025-10-14
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2025/10/pexels-photo-6834428.jpeg?fit=867%2C1300&ssl=1
categories: ["mysql", "SQL"]
tags: ["Analytics", "LAG", "window functions"]
type: post
lang: en
---

# Mastering SQL Date Extraction and Monthly Trends Using LAG()

## Introduction

In SQL, analyzing date-based data is one of the most common requirements.
In this post, we’ll explore **two practical SQL examples**:

1. Extracting the **year** from a date in both **MySQL** and **SQL Server (MSSQL)**.
2. Using the **LAG() window function** to track **monthly changes** in customer orders.

Both examples include sample tables, inserts, and complete queries you can run directly in your SQL environment.

Related[30 SQL Practice Queries with Solutions — Run Them Live](https://srinimf.com/2026/10/01/30-sql-practice-queries-with-solutions-run-them-live/)

## 🧩 Query 1 — Extract Year from a Date

When dealing with orders, invoices, or events, you’ll often need to extract just the **year** from a date field. SQL provides built-in functions for this purpose.

### 🧱 Create Table and Sample Data

```
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(50),
    order_date DATE
);

INSERT INTO orders VALUES
(1, 'Alice', '2023-05-14'),
(2, 'Bob',   '2024-02-21'),
(3, 'Carol', '2025-09-10');
```

### ✅ MySQL Query

```
SELECT 
    order_id,
    customer_name,
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;
```

Alternate:

```
SELECT YEAR(order_date) AS order_year FROM orders;
```

### ✅ SQL Server (MSSQL) Query

```
SELECT 
    order_id,
    customer_name,
    order_date,
    YEAR(order_date) AS order_year
FROM orders;
```

### 🧠 Output

| order_id | customer_name | order_date | order_year |
| --- | --- | --- | --- |
| 1 | Alice | 2023-05-14 | 2023 |
| 2 | Bob | 2024-02-21 | 2024 |
| 3 | Carol | 2025-09-10 | 2025 |

### 💡 Explanation

- `EXTRACT(YEAR FROM order_date)` → Works in **MySQL**.
- `YEAR(order_date)` → Works in both **MySQL** and **SQL Server**.
- These functions are extremely useful for grouping or filtering data by year.

## 📊 Query 2 — Analyze Monthly Customer Order Trends Using `LAG()`

When analyzing customer data, it’s common to check whether the number of orders per month has increased or decreased compared to the previous month.
The **LAG()** function helps achieve this by referencing the previous row’s value in a window.

### 🧱 Create Table and Insert Data

```
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
);

INSERT INTO orders VALUES
(1, 101, '2024-01-10'),
(2, 101, '2024-01-25'),
(3, 101, '2024-02-15'),
(4, 101, '2024-03-10'),
(5, 102, '2024-01-12'),
(6, 102, '2024-03-08'),
(7, 103, '2024-02-20');
```

### ✅ MySQL Query

```
WITH monthly_counts AS (
  SELECT 
      customer_id,
      DATE_FORMAT(order_date, '%Y-%m-01') AS month_start,
      COUNT(*) AS cnt
  FROM orders
  GROUP BY customer_id, month_start
),
increase_check AS (
  SELECT 
      customer_id,
      month_start,
      cnt,
      LAG(cnt) OVER (PARTITION BY customer_id ORDER BY month_start) AS prev_cnt
  FROM monthly_counts
)
SELECT 
    customer_id,
    month_start,
    cnt,
    prev_cnt,
    cnt - COALESCE(prev_cnt, 0) AS change_in_orders
FROM increase_check
ORDER BY customer_id, month_start;
```

### ✅ SQL Server (MSSQL) Query

```
WITH monthly_counts AS (
  SELECT 
      customer_id,
      DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0) AS month_start,
      COUNT(*) AS cnt
  FROM orders
  GROUP BY customer_id, DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0)
),
increase_check AS (
  SELECT 
      customer_id,
      month_start,
      cnt,
      LAG(cnt) OVER (PARTITION BY customer_id ORDER BY month_start) AS prev_cnt
  FROM monthly_counts
)
SELECT 
    customer_id,
    month_start,
    cnt,
    prev_cnt,
    cnt - COALESCE(prev_cnt, 0) AS change_in_orders
FROM increase_check
ORDER BY customer_id, month_start;
```

---

### 🧠 Output

| customer_id | month_start | cnt | prev_cnt | change_in_orders |
| --- | --- | --- | --- | --- |
| 101 | 2024-01-01 | 2 | NULL | 2 |
| 101 | 2024-02-01 | 1 | 2 | -1 |
| 101 | 2024-03-01 | 1 | 1 | 0 |
| 102 | 2024-01-01 | 1 | NULL | 1 |
| 102 | 2024-03-01 | 1 | 1 | 0 |
| 103 | 2024-02-01 | 1 | NULL | 1 |

### 💡 Explanation

- `LAG(cnt)` fetches the **previous month’s** order count for each customer.
- `COALESCE(prev_cnt, 0)` replaces `NULL` with `0` for the first month.
- `cnt - prev_cnt` calculates the **change in order count**.
- The result shows how a customer’s activity evolved month over month.

## 🎯 Summary

| Concept | Function | Use Case |
| --- | --- | --- |
| Extract year | `EXTRACT(YEAR)` / `YEAR()` | Get the year from a date |
| Monthly trend | `LAG()` | Compare current and previous months |
| Grouping | `GROUP BY` | Aggregate monthly counts |
| Handling nulls | `COALESCE()` | Replace missing previous values |

### 🚀 Key Takeaways

- `YEAR()` works across multiple databases and is simple for reporting.
- `LAG()` is one of the most powerful **[window functions](https://srinimf.com/2025/05/03/11-top-mysql-window-functions-with-use-cases/)** for time-based analytics.
- Combining these techniques helps you uncover **yearly and monthly trends** in data efficiently.

## SQL Query to Find Employees Who Achieved 80% or More of Their Target

## Introduction

In business scenarios, it’s common [to measure](https://srinimf.com/2022/07/11/how-to-measure-speed-of-python-code/) performance based on **target vs. actual** numbers.
For example, sales teams often have a monthly sales **target**, and you might want to find out who achieved **at least 80% of that target**.

In this article, we’ll build a SQL example step-by-step to identify such employees.

## 🧱 Create Table and Insert Data

Let’s start with a simple dataset.

```
CREATE TABLE employee_sales (
    emp_id INT,
    emp_name VARCHAR(50),
    target_amount DECIMAL(10,2),
    achieved_amount DECIMAL(10,2)
);

INSERT INTO employee_sales VALUES
(1, 'Alice', 10000, 9500),
(2, 'Bob',   12000, 8000),
(3, 'Carol', 15000, 10000),
(4, 'David', 20000, 16000),
(5, 'Emma',  18000, 13000);
```

## ✅ SQL Query — Employees Who Achieved at Least 80% of Their Target

### 💡 Using `CASE WHEN` and Simple Arithmetic

```
SELECT 
    emp_id,
    emp_name,
    target_amount,
    achieved_amount,
    ROUND((achieved_amount / target_amount) * 100, 2) AS achievement_percent,
    CASE 
        WHEN (achieved_amount / target_amount) >= 0.8 THEN 'Achieved 80% or More'
        ELSE 'Below 80%'
    END AS performance_status
FROM employee_sales;
```

---

### 🧠 Output

| emp_id | emp_name | target_amount | achieved_amount | achievement_percent | performance_status |
| --- | --- | --- | --- | --- | --- |
| 1 | Alice | 10000 | 9500 | 95.00 | Achieved 80% or More |
| 2 | Bob | 12000 | 8000 | 66.67 | Below 80% |
| 3 | Carol | 15000 | 10000 | 66.67 | Below 80% |
| 4 | David | 20000 | 16000 | 80.00 | Achieved 80% or More |
| 5 | Emma | 18000 | 13000 | 72.22 | Below 80% |

## 🧩 Explanation

| Clause | Purpose |
| --- | --- |
| `ROUND((achieved_amount / target_amount) * 100, 2)` | Calculates achievement % rounded to two decimals |
| `CASE WHEN ... THEN ... ELSE ... END` | Categorizes each employee’s performance |
| `>= 0.8` | Checks if 80% target is met or exceeded |

This query helps you instantly see who’s performing well and who needs improvement.

## 🧮 Optional — Filter Only High Performers

If you want to list **only those who achieved at least 80%**, add a `WHERE` clause:

```
SELECT 
    emp_id,
    emp_name,
    target_amount,
    achieved_amount,
    ROUND((achieved_amount / target_amount) * 100, 2) AS achievement_percent
FROM employee_sales
WHERE (achieved_amount / target_amount) >= 0.8;
```

### 🧠 Filtered Output

| emp_id | emp_name | target_amount | achieved_amount | achievement_percent |
| --- | --- | --- | --- | --- |
| 1 | Alice | 10000 | 9500 | 95.00 |
| 4 | David | 20000 | 16000 | 80.00 |

## 🎯 Summary

| Concept | Function | Description |
| --- | --- | --- |
| Percentage Calculation | `(achieved / target) * 100` | Computes performance percentage |
| `CASE WHEN` | Conditional logic | Classifies employees |
| `ROUND()` | Rounds decimals | Keeps result clean |
| `WHERE` | Filter condition | Shows only 80% achievers |

### 🚀 Key Takeaways

- Use simple math and `CASE WHEN` for performance analysis.
- Works on all major SQL engines — MySQL, PostgreSQL, SQL Server, and Oracle.
- Can be easily extended for monthly or team-wise analysis.

## **Calculating last 3 months, summing sales vs target, and comparing percentages**

Here’s your **single combined SQL query** that calculates employees who achieved **≥ 80%** of their total target over the **last 3 months**, assuming `month` is stored in `YYYY-MM` format. (Databricks SQL)

### **Final Single Query (MySQL / Databricks SQL Compatible)**

Table data

```
CREATE TABLE sales (
    sale_id INT,
    empid INT,
    amount DECIMAL(10,2),
    date DATE
);

CREATE TABLE target (
    empid INT,
    month STRING,
    tgt_amount DECIMAL(10,2)
);

INSERT INTO sales (sale_id, empid, amount, date) VALUES
(1, 101, 1200.00, '2025-08-10'),
(2, 101, 1500.00, '2025-09-05'),
(3, 101, 1000.00, '2025-10-01'),
(4, 102, 800.00,  '2025-08-12'),
(5, 102, 700.00,  '2025-09-07'),
(6, 102, 1000.00, '2025-10-03'),
(7, 103, 500.00,  '2025-08-10'),
(8, 103, 400.00,  '2025-09-10'),
(9, 103, 300.00,  '2025-10-01');

INSERT INTO target (empid, month, tgt_amount) VALUES
(101, '2025-08', 3000.00),
(101, '2025-09', 3200.00),
(101, '2025-10', 3500.00),
(102, '2025-08', 2000.00),
(102, '2025-09', 2500.00),
(102, '2025-10', 2700.00),
(103, '2025-08', 1000.00),
(103, '2025-09', 1200.00),
(103, '2025-10', 1300.00);
```

```
WITH last_3_months AS (
    SELECT DISTINCT month
    FROM target
    ORDER BY month DESC
    LIMIT 3
),
emp_sales AS (
    SELECT 
        s.empid, 
        SUM(s.amount) AS total_sales
    FROM sales s
    JOIN last_3_months l3 
      ON DATE_FORMAT(s.date, 'yyyy-MM') = l3.month
    GROUP BY s.empid
),
emp_targets AS (
    SELECT 
        t.empid, 
        SUM(t.tgt_amount) AS total_target
    FROM target t
    JOIN last_3_months l3 
      ON t.month = l3.month
    GROUP BY t.empid
)
SELECT 
    s.empid,
    s.total_sales,
    t.total_target,
    ROUND((s.total_sales / t.total_target) * 100, 2) AS achievement_percent
FROM emp_sales s
JOIN emp_targets t 
  ON s.empid = t.empid
WHERE s.total_sales >= 0.8 * t.total_target
ORDER BY achievement_percent DESC;
```

### 💡 **Explanation**

| Step | Description |
| --- | --- |
| `last_3_months` | Finds the latest 3 distinct months from the `target` table. |
| `emp_sales` | Sums employee sales for those 3 months. |
| `emp_targets` | Sums targets for those same 3 months. |
| Final SELECT | Joins the two results and filters employees who met ≥ 80% of the target. |

### 🧪 Example Data

**Table: `target`**

| empid | month | tgt_amount |
| --- | --- | --- |
| 101 | 2025-08 | 1000 |
| 101 | 2025-09 | 1200 |
| 101 | 2025-10 | 800 |
| 102 | 2025-08 | 1500 |
| 102 | 2025-09 | 1300 |
| 102 | 2025-10 | 1700 |

**Table: `sales`**

| empid | date | amount |
| --- | --- | --- |
| 101 | 2025-08-15 | 900 |
| 101 | 2025-09-12 | 1100 |
| 101 | 2025-10-05 | 700 |
| 102 | 2025-08-20 | 1600 |
| 102 | 2025-09-10 | 1400 |
| 102 | 2025-10-22 | 1900 |

**✅ Output**

| empid | total_sales | total_target | achievement_percent |
| --- | --- | --- | --- |
| 102 | 4900 | 4500 | 108.89 |
| 101 | 2700 | 3000 | 90.00 |

Both employees are ≥ 80% achievers 🎯

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2025%2F10%2F14%2Fmastering-sql-date-extraction-and-monthly-trends-using-lag%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2025%2F10%2Fpexels-photo-6834428.jpeg%3Ffit%3D800%252C1200%26ssl%3D1&description=Mastering%20SQL%20Date%20Extraction%20and%20Monthly%20Trends%20Using%20LAG%28%29)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2025/10/14/mastering-sql-date-extraction-and-monthly-trends-using-lag/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2025/10/14/mastering-sql-date-extraction-and-monthly-trends-using-lag/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2025/10/14/mastering-sql-date-extraction-and-monthly-trends-using-lag/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2025/10/14/mastering-sql-date-extraction-and-monthly-trends-using-lag/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Mastering%20SQL%20Date%20Extraction%20and%20Monthly%20Trends%20Using%20LAG%28%29&body=https%3A%2F%2Fsrinimf.com%2F2025%2F10%2F14%2Fmastering-sql-date-extraction-and-monthly-trends-using-lag%2F&share=email)
-

### Like this:

Like Loading…