-- Select top 3 customers based on total order score

SELECT TOP 3 
    customer_id, 
    customer_name, 
    score 
FROM customers 
ORDER BY score DESC;

[ROLE]
Act as a Senior Database Architect and SQL Performance Tuning Expert.

[CONTEXT]
I am a Data Analyst working with a Microsoft SQL Server enterprise database. I am working on analytical reporting queries over a table containing tens of millions of sales transactions.

[TASK]
Explain the concept of SQL Window Functions and demonstrate how to apply them for analytical reporting.

[SPECIFICATIONS]
1. Explain the operational difference between window functions and GROUP BY.
2. Provide the generic syntax structure (OVER, PARTITION BY, ORDER BY).
3. Demonstrate the top 3 production use cases (Ranking, Running Totals, Moving Averages).
4. Include clean T-SQL code snippets for each use case.
5. Highlight performance pitfalls when executing over large tables.

[TONE]
Professional, precise, structured, and direct.

Act as an SQL Developer. I am using SQL Server.

Database Schema:
- Table 'orders': order_id (INT), customer_id (INT), sales_amount (DECIMAL), order_date (DATE)
- Table 'customers': customer_id (INT), full_name (VARCHAR), country (VARCHAR)

Task:
Write an SQL script to rank customers based on their total order sales amount.

Specifications:
1. Output fields: customer_id, full_name, country, total_sales, sales_rank.
2. Include brief comments on complex parts (avoid commenting obvious code).
3. Write THREE distinct query variants to achieve this task (e.g., using CTEs, Subqueries, and direct Window Functions).
4. Evaluate all 3 versions for Readability vs. Performance trade-offs.

-- Version 1: Common Table Expression (CTE) - High Readability
WITH CustomerSales AS (
    SELECT 
        c.customer_id,
        c.full_name,
        c.country,
        SUM(o.sales_amount) AS total_sales
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.full_name, c.country
)
SELECT 
    customer_id,
    full_name,
    country,
    total_sales,
    DENSE_RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM CustomerSales;

-- Version 2: Subquery Approach - Intermediate
SELECT 
    cs.customer_id,
    cs.full_name,
    cs.country,
    cs.total_sales,
    DENSE_RANK() OVER (ORDER BY cs.total_sales DESC) AS sales_rank
FROM (
    SELECT 
        c.customer_id,
        c.full_name,
        c.country,
        SUM(o.sales_amount) AS total_sales
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.full_name, c.country
) cs;

-- Version 3: Direct Group By with Window Function - Maximum Compactness
SELECT 
    c.customer_id,
    c.full_name,
    c.country,
    SUM(o.sales_amount) AS total_sales,
    DENSE_RANK() OVER (ORDER BY SUM(o.sales_amount) DESC) AS sales_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.full_name, c.country;

The following SQL Server query is long, hard to read, and contains multiple CTEs that seem redundant.

Tasks:
1. Refactor the script to improve overall readability.
2. Identify and eliminate redundant CTEs or subqueries, consolidating the logic.
3. Keep comments concise and focused on business rules.
4. Explain each architectural improvement step-by-step so I can avoid these issues in the future.

Input Query:
[Paste raw script here]

I have a slow-running SQL Server query executing against a table with millions of rows.

Task:
1. Review the query for anti-patterns and performance bottlenecks.
2. Provide an optimized rewrite following SQL performance best practices.
3. Explain each optimization applied (e.g., avoiding non-sargable functions in WHERE clauses, replacing correlated subqueries, minimizing scan operators).

Input Query:
SELECT order_id, customer_id, sales_amount 
FROM orders 
WHERE LOWER(status) = 'completed' 
  AND YEAR(order_date) = 2023 
  AND customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

-- Optimized Production Query
SELECT 
    o.order_id, 
    o.customer_id, 
    o.sales_amount 
FROM orders o
WHERE o.status = 'completed' -- Assumes standard casing or case-insensitive collation
  AND o.order_date >= '2023-01-01' 
  AND o.order_date < '2024-01-01' -- Sargable date-range filtering allows index seek
  AND EXISTS (
      SELECT 1 
      FROM customers c 
      WHERE c.customer_id = o.customer_id 
        AND c.country = 'USA'
  );

I am attaching/pasting the text execution plan for an SQL Server query.

Task:
1. Walk through the execution flow step-by-step.
2. Identify major performance bottlenecks (e.g., Table Scans, Index Scans vs Seeks, Implicit Conversions, Heavy Hash Spills, or Nested Loop bottlenecks).
3. Provide precise schema or query index modifications to eliminate the identified bottlenecks.

Execution Plan Details / Context:
- Table 'orders_archive' shows a Table Scan taking 82% of batch cost.
- Join operator indicates a Nested Loop between two large datasets.

My SQL Server query fails upon execution.

Context:
Error Code / Message:
"Msg 8120, Level 16, State 1: Column 'sales.orders.sales_amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Task:
1. Explain the underlying root cause of this error in plain terms.
2. Pinpoint the exact location in the query triggering the issue.
3. Provide the corrected SQL script.

Input Query:
SELECT customer_id, country, sales_amount, SUM(sales_amount)
FROM sales.orders
GROUP BY customer_id;

I inherited an SQL script containing a complex logic pattern (e.g., Recursive CTE) and need to understand how the output dataset is constructed.

Task:
1. Break down how the database engine executes this query step-by-step.
2. Explain the anchor evaluation vs. the recursive iteration process.
3. Identify the explicit exit/termination condition to explain how infinite loops are prevented.

Input Query:
WITH RECURSIVE SequenceGenerator AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1
    FROM SequenceGenerator
    WHERE number < 20
)
SELECT number FROM SequenceGenerator;

Re-style the following SQL script to match enterprise standard readability guidelines.

Specifications:
1. Capitalize all standard SQL keywords (SELECT, FROM, WHERE, JOIN, OVER, CTE).
2. Align column aliases consistently.
3. Maintain compact formatting without injecting unnecessary double newlines.
4. Standardize string concatenation and conditional expressions.

Input Query:
[Paste messy code here]

The following analytical SQL script lacks documentation and comments.

Task:
1. Add a high-level header block comment detailing the overall business purpose of the script.
2. Add inline comments strictly where business logic or non-obvious transformations occur (do NOT comment basic syntax).
3. Output a markdown documentation block summarizing:
   - Input Tables & Dependencies
   - Business Logic Rules Applied
   - Target Output Metrics

Input Query:
[Paste query here]

Optimize the following SQL Server DDL script for data integrity, performance, and naming consistency.

Task:
1. **Naming Conventions**: Check for consistent casing (PascalCase / snake_case).
2. **Data Types**: Suggest optimal types (e.g., replacement of generic FLOAT/INT with DECIMAL/SMALLINT where appropriate).
3. **Data Integrity**: Add essential constraints (PRIMARY KEY, FOREIGN KEY, CHECK constraints, NOT NULL).
4. **Indexing Strategy**: Recommend non-clustered indexes for foreign keys and common query predicates.

Input DDL:
CREATE TABLE employees (
    emp_id INT,
    first_name VARCHAR(50),
    gender CHAR(1),
    salary FLOAT,
    birth_date DATE
);

CREATE TABLE dbo.Employees (
    EmployeeID INT IDENTITY(1,1) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    Gender CHAR(1) NOT NULL,
    Salary DECIMAL(12, 2) NOT NULL,
    BirthDate DATE NOT NULL,
    
    -- Constraints
    CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID),
    CONSTRAINT CK_Employees_Gender CHECK (Gender IN ('M', 'F', 'U')),
    CONSTRAINT CK_Employees_Salary CHECK (Salary >= 0.00),
    CONSTRAINT CK_Employees_BirthDate CHECK (BirthDate <= GETDATE())
);

-- Indexing Recommendation for performance
CREATE NONCLUSTERED INDEX IX_Employees_BirthDate 
ON dbo.Employees(BirthDate);

SELECT 
    customer_id, 
    COALESCE(middle_name, '') AS middle_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    NOW() AS query_time
FROM customers
LIMIT 10;

Act as an Expert Data Engineering Instructor.

Task:
Create a comprehensive, 4-phase structured learning roadmap for mastering SQL tailored specifically for Data Analytics.

Specifications:
1. Break down into 4 clear phases: Fundamentals, Intermediate, Advanced Analytics, Real-World Scenarios.
2. Under each phase, list core concepts, key functions, and practical project tasks.
3. Focus heavily on analytical operations (aggregations, joins, window functions, CTEs).

Explain the concept of SQL Window Functions to a beginner student.

Specifications:
1. Provide a real-world non-technical mental analogy to make the concept intuitive.
2. Explain *why* traditional GROUP BY queries are insufficient for row-level detail retainment.
3. Show a basic syntax breakdown (OVER, PARTITION BY, ORDER BY).

Analogy Output:

Compare SQL Window Functions versus GROUP BY Aggregations.

Task:
1. Explain structural differences in handling row detail.
2. Detail when to use which pattern.
3. Summarize all differences in a clear Markdown table.

Act as an SQL Technical Interviewer and Practice Coach.

Task:
Conduct an interactive SQL mock technical interview with me focusing on Window Functions and Joins.

Rules of Interaction:
1. Present ONE question at a time.
2. Provide a small, schema sample dataset for context.
3. Wait for my code submission before moving forward.
4. Evaluate my answer strictly: analyze correctness, performance, and edge-case vulnerabilities.
5. Provide a score, suggest improvements, and ask if I am ready for the next question.

[Interviewer AI]: 
Question 1: Given a 'sales' table (sales_id, employee_id, amount, sale_date), 
write a query to calculate each employee's running total of sales over time.

[User Submission]: 
SELECT 
    employee_id, 
    sale_date, 
    amount, 
    SUM(amount) OVER (PARTITION BY employee_id) AS running_total
FROM sales;

[Interviewer AI Evaluation]:
Result: INCORRECT.
Feedback: You applied `PARTITION BY employee_id`, which calculates the grand total 
sales per employee across all time, but missed the dynamic running accumulator! 
To create a running total, you must include an `ORDER BY` clause inside the OVER() window.

Corrected Query:
SELECT 
    employee_id, 
    sale_date, 
    amount, 
    SUM(amount) OVER (
        PARTITION BY employee_id 
        ORDER BY sale_date
    ) AS running_total
FROM sales;

Ready for Question 2? (Yes/No)