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.

🧩 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 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 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 🎯