**Data Warehousing Concepts & ETL Processes

Building upon foundational knowledge, we'll explore powerful SQL functions designed for intricate data analysis. This includes aggregate functions with GROUP BY and HAVING clauses, along with advanced string manipulation, date and time functions, and mathematical operations.

Aggregate Functions with GROUP BY and HAVING: Mastering these constructs allows you to summarize data based on specific criteria. The HAVING clause filters the grouped results, enabling you to extract specific insights.

• Example: Analyzing sales data to identify top-performing product categories after applying a discount.

  sql SELECT category, SUM(revenue) AS total_revenue FROM sales WHERE discount > 0 GROUP BY category HAVING SUM(revenue) > 10000; -- Filter categories with revenue exceeding $10,000

Advanced String Manipulation: Working with textual data often requires more than basic string functions. Explore functions for substring extraction, pattern matching (LIKE with wildcards), and string replacement. This is important for tasks like data cleaning and feature engineering.

• Example: Extracting email domains from customer data for targeted marketing campaigns.

  sql SELECT customer_id, email, SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email) - CHARINDEX('@', email)) AS email_domain FROM customers;

Date and Time Functions: Utilize functions for extracting specific date components, calculating date differences, and formatting dates. This is invaluable when analyzing trends over time, seasonal patterns and any time-series analysis.

• Example: Calculating the age of customers based on their date of birth.

  sql SELECT customer_id, dob, DATEDIFF(year, dob, GETDATE()) AS age FROM customers;

Mathematical Operations: Functions like ROUND, CEILING, FLOOR, and trigonometric functions are essential for financial modeling, statistical analysis, and data transformation.

• Example: Rounding a calculated price and then applying a discount.

  sql SELECT product_id, ROUND(price * (1 - discount), 2) AS discounted_price FROM products;

Window functions are a powerful feature in SQL that allows you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions (like SUM and AVG) that collapse rows into a single result, window functions return a value for each row based on the window of rows defined.

Key Window Functions and their Applications:

• ROW_NUMBER(): Assigns a unique sequential integer to each row within a partition.
• RANK() & DENSE_RANK(): Assigns a rank to each row based on the ordering of a column. RANK may have gaps in the ranking if there are ties, while DENSE_RANK assigns consecutive ranks.
• NTILE(n): Divides the rows into 'n' groups, also know as 'buckets'. Good for quartile or decile analysis.
• LAG() & LEAD(): Accesses values from the previous or subsequent rows in a result set.
• SUM(), AVG(), COUNT() (with OVER()): Performs aggregation within the defined window.

Syntax: function_name(column_name) OVER (PARTITION BY partition_column ORDER BY order_column)

Example: Calculating the running total sales for each product, ordered by date:

```sql
SELECT
    product_id,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales_data;
```

Example: Determining the top-performing sales representative each month.

```sql
SELECT
    sale_rep_id,
    sale_date,
    sale_amount,
    RANK() OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY sale_amount DESC) AS sales_rank
FROM sales;
WHERE sales_rank = 1; -- Filter for top salesperson each month
```

CTEs are temporary, named result sets defined within a single SQL statement. They greatly enhance query readability, modularity, and maintainability, especially for intricate data transformations. Think of them as views, but limited to the scope of a single query.

Benefits of using CTEs:

• Improved Readability: Break down complex queries into logical blocks.
• Modularity: Reuse the result of a subquery multiple times.
• Performance Optimization (in some cases): The database optimizer can sometimes handle CTEs more efficiently.

Syntax:

```sql
WITH
    cte_name AS (SELECT statement),
    another_cte AS (SELECT statement)
SELECT ... FROM cte_name JOIN another_cte ...;
```

Example: Finding customers who made purchases above the average purchase amount.

```sql
WITH average_purchases AS (
    SELECT customer_id, AVG(purchase_amount) AS avg_purchase
    FROM purchases
    GROUP BY customer_id
)
SELECT p.customer_id, p.purchase_amount
FROM purchases p
JOIN average_purchases ap ON p.customer_id = ap.customer_id
WHERE p.purchase_amount > ap.avg_purchase;
```

Recursive CTEs: Recursive CTEs enable querying hierarchical data, such as organizational charts or bill of materials, by referencing themselves within the CTE definition. This allows processing data across multiple levels of hierarchy.

```sql
WITH RECURSIVE employee_hierarchy AS (
  -- Anchor member: Select the top-level employees
  SELECT employee_id, employee_name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member: Select employees and their managers
  SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1 AS level
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, employee_name, manager_id, level
FROM employee_hierarchy
ORDER BY level, employee_name; -- Ordering for clear hierarchy representation
```

SQL can be directly used to generate datasets suitable for creating data visualizations. While SQL doesn't render the visualizations itself, the queries prepare the data to be used in BI tools like Tableau, Power BI, or custom Python scripts with libraries like matplotlib or seaborn.

Key Steps:

1. Define the Business Question: Clearly identify what insights you want to reveal.
2. Identify Data Sources: Determine the tables and columns needed.
3. Construct the SQL Query: Use the learned techniques to aggregate, filter, and transform the data.
4. Export or Feed the Data: Get the data into your visualization tool.
5. Create Visualizations: Design charts, graphs, and dashboards to communicate findings.

Example: Sales Performance Dashboard

• Business Question: Analyze sales trends and identify top-performing products.
• Data Source: sales_data table (product_id, sale_date, sale_amount).

• SQL Query:

  sql SELECT product_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(sale_amount) AS total_sales FROM sales_data GROUP BY product_id, DATE_TRUNC('month', sale_date) ORDER BY sale_month, total_sales DESC;

• Visualization: Use a time-series chart in your BI tool to display monthly sales trends for each product, and create a bar chart for the top products. Use the table to build Pivot tables as well.

Example: Customer Segmentation Report

• Business Question: Group customers into segments based on purchase frequency and average order value.
• Data Source: purchases table (customer_id, purchase_date, purchase_amount).

• SQL Query:

  sql WITH customer_summary AS ( SELECT customer_id, COUNT(DISTINCT purchase_date) AS purchase_frequency, AVG(purchase_amount) AS avg_order_value FROM purchases GROUP BY customer_id ) SELECT customer_id, purchase_frequency, avg_order_value, CASE WHEN purchase_frequency > 10 AND avg_order_value > 100 THEN 'High Value Customers' WHEN purchase_frequency > 5 AND avg_order_value > 50 THEN 'Medium Value Customers' ELSE 'Low Value Customers' END AS customer_segment FROM customer_summary;

• Visualization: A scatter plot showing average order value vs. purchase frequency, with the points colored according to the customer segment. You could also use a bar chart to show the count of customers in each segment.