Claude vs DeepSeek vs Gemini: SQL Tutorial

Prompt

SQL for data analysis - essential queries and techniques

ClaudeAnthropic

SQL for data analysis - essential queries and techniques

SQL for Data Analysis - Essential Queries and Techniques

Basic Analysis Queries

Aggregation and Grouping

Window Functions

Date and Time Analysis

Advanced Filtering and Conditions

Statistical Functions

Data Cleaning Techniques

Performance Optimization

Basic Analysis Queries

Data Exploration

-- Basic table structure and data preview
SELECT * FROM sales LIMIT 10;
-- Column information and data types
DESCRIBE sales;
-- or
SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'sales';
-- Count total records
SELECT COUNT(*) as total_records FROM sales;-- Check for duplicates
SELECT customer_id, product_id, COUNT(*) as duplicate_count
FROM sales 
GROUP BY customer_id, product_id 
HAVING COUNT(*) > 1;

Basic Statistics

-- Summary statistics for numeric columns
SELECT 
    COUNT(amount) as count,
    MIN(amount) as min_amount,
    MAX(amount) as max_amount,
    AVG(amount) as avg_amount,
    STDDEV(amount) as std_dev,
    SUM(amount) as total_amount
FROM sales;-- Distinct value counts
SELECT 
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT product_id) as unique_products,
    COUNT(DISTINCT category) as unique_categories
FROM sales;

Aggregation and Grouping

GROUP BY with Multiple Dimensions

-- Sales by category and month
SELECT 
    category,
    DATE_FORMAT(sale_date, '%Y-%m') as month,
    COUNT(*) as transaction_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_transaction_value
FROM sales 
GROUP BY category, DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY month, total_sales DESC;-- Customer segmentation analysis
SELECT 
    customer_segment,
    COUNT(DISTINCT customer_id) as customer_count,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value,
    COUNT(*) as total_orders,
    SUM(amount) / COUNT(DISTINCT customer_id) as revenue_per_customer
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY customer_segment;

HAVING Clause for Filtered Aggregations

-- High-value customers (total purchases > $1000)
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_spent,
    AVG(amount) as avg_order_value
FROM sales 
GROUP BY customer_id 
HAVING SUM(amount) > 1000
ORDER BY total_spent DESC;-- Products with consistent high performance
SELECT 
    product_id,
    COUNT(*) as sales_count,
    AVG(amount) as avg_price
FROM sales 
GROUP BY product_id 
HAVING COUNT(*) > 10 AND AVG(amount) > 50;

Window Functions

Ranking and Row Numbers

```sql
-- Top 3 products by sales in each category
SELECT
category,
product_id,
total_sales,
RANK() OVER (PARTITION BY category ORDER BY totalsales DESC) as rankin_category
FROM (
SELECT
category,
product_id,
SUM(amount) as total_sales
FROM sales
GROUP BY category, product_id
) ranked
WHERE rankincategory <= 3;

-- Running totals and percentiles
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY saledate) as runningtotal,
ROWNUMBER() OVER (ORDER BY amount DESC) as salesrank,

22:46

DeepSeekDeepSeek

SQL for data analysis - essential queries and techniques

Of course! SQL is the fundamental language for working with relational databases, and mastering it is crucial for any data analyst. This guide will walk you through essential queries and techniques, from the absolute basics to more advanced analytical functions.

We'll use a sample database for an e-commerce company with the following tables:

customers: customerid, firstname, lastname, city, signupdate

products: productid, productname, category, price

orders: orderid, customerid, order_date

orderitems: orderitemid, orderid, product_id, quantity

1. The Absolute Fundamentals (The "Must-Knows")

These are the building blocks of almost every query you'll write.

a) SELECT & FROM

Retrieves data from a table.

SELECT * FROM customers; -- Gets all columns
SELECT first_name, last_name FROM customers; -- Gets specific columns

b) WHERE

Filters rows based on a condition.

SELECT * FROM products WHERE category = 'Electronics';
SELECT * FROM orders WHERE order_date >= '2023-10-01';
-- Use AND, OR for multiple conditions
SELECT * FROM products WHERE category = 'Electronics' AND price > 500;

c) Aggregate Functions & GROUP BY

Summarizes data. Common functions: COUNT(), SUM(), AVG(), MIN(), MAX().

-- How many customers do we have?
SELECT COUNT(*) FROM customers;
-- What's the total revenue from the 'order_items' table?
SELECT SUM(quantity * price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id;-- What is the average price of products in each category?
SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category;

Crucial Rule: When using GROUP BY, all columns in the SELECT clause must either be in the GROUP BY clause or be an aggregate function.

d) HAVING

Filters groups created by GROUP BY. (WHERE filters rows, HAVING filters groups).

-- Find categories with an average product price over $100
SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

e) ORDER BY

Sorts the result set.

-- Show products, sorted by price (highest first)
SELECT product_name, price
FROM products
ORDER BY price DESC;-- Show top 10 most recent orders
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10; -- (LIMIT is used in MySQL, PostgreSQL; use TOP in SQL Server, FETCH FIRST in Oracle)

f) JOINs

Combine rows from two or more tables based on a related column. This is the heart of relational data analysis.

INNER JOIN: Returns records that have matching values in both tables.

-- Get all orders with customer information
    SELECT o.order_id, o.order_date, c.first_name, c.last_name
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id;

LEFT JOIN: Returns all records from the left table, and the matched records from the right table. Results from the right with no match are NULL.

-- Get all customers and their orders (even if they haven't placed any)
    SELECT c.first_name, c.last_name, o.order_id
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id;

2. Essential Techniques for Analysis

a) Using CASE for Conditional Logic

The CASE statement is the SQL equivalent of an IF-THEN-ELSE statement. It's incredibly powerful for creating calculated columns.

-- Categorize customers based on their signup date
SELECT first_name, last_name, signup_date,
    CASE
        WHEN signup_date > '2023-09-01' THEN 'New'
        WHEN signup_date > '2023-01-01' THEN 'Active'
        ELSE 'Legacy'
    END AS customer_segment
FROM customers;-- Create a flag for high-value products
SELECT product_name, price,
    CASE
        WHEN price > 1000 THEN 'Premium'
        WHEN price > 100 THEN 'Mid-Range'
        ELSE 'Budget'
    END AS price_tier
FROM products;

b) Date and Time Functions

Analyzing trends over time is a core analytical task.

-- Extract the year and month from a date (PostgreSQL syntax)
SELECT order_id, order_date,
       EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;-- Calculate the number of days between two dates (e.g., signup and first order)
SELECT c.customer_id,
       MIN(o.order_date) - c.signup_date AS days_to_first_order
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.signup_date;

Note: Date functions vary significantly by database (MySQL: YEAR(), MONTH(); SQL Server: DATEPART()). Always check your database's documentation.

3. Advanced Analytical Queries (Window Functions)

Window functions perform a calculation across a set of table rows that are somehow related to the current row. They are a game-changer for data analysis.

a) ROWNUMBER(), RANK(), DENSERANK()

Assign a unique rank or row number to rows within a partition.

-- Rank products by price within their category
SELECT product_name, category, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

ROWNUMBER() gives unique numbers. RANK() leaves gaps in the sequence for ties. DENSERANK() does not leave gaps.

b) Running Totals with SUM()

-- Calculate the running total of daily revenue
SELECT order_date,
       SUM(quantity * price) AS daily_revenue,
       SUM(SUM(quantity * price)) OVER (ORDER BY order_date) AS running_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY order_date
ORDER BY order_date;

c) LAG() and LEAD()

Access data from a previous or subsequent row in the same result set. Perfect for calculating month-over-month growth.

-- Compare each month's revenue with the previous month's
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS revenue_month,
    SUM(quantity * price) AS monthly_rev
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT revenue_month,
       monthly_rev,
       LAG(monthly_rev) OVER (ORDER BY revenue_month) AS previous_month_rev,
       (monthly_rev - LAG(monthly_rev) OVER (ORDER BY revenue_month)) / LAG(monthly_rev) OVER (ORDER BY revenue_month) * 100 AS growth_percentage
FROM monthly_revenue
ORDER BY revenue_month;

4. Structuring Complex Queries

a) Common Table Expressions (CTEs)

CTEs create temporary, named result sets that you can reference within your main query. They make complex queries much more readable and modular.

-- Find the top-spending customer in each city
WITH CustomerSpending AS (
    SELECT
        c.customer_id,
        c.first_name,
        c.last_name,
        c.city,
        SUM(oi.quantity * p.price) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.city
),
RankedSpending AS (
    SELECT *,
           RANK() OVER (PARTITION BY city ORDER BY total_spent DESC) AS spending_rank
    FROM CustomerSpending
)
SELECT * FROM RankedSpending
WHERE spending_rank = 1;

Best Practices for Data Analysts

Start with a Plan: Write down the question you're trying to answer in plain English before writing any code.

Build Incrementally: Start with a simple SELECT * FROM table to see your data. Then add JOINs, then WHERE filters, then GROUP BY, etc. This makes debugging much easier.

Use Aliases: Always use clear table aliases (like c for customers) to make your queries cleaner.

**Format Your

22:46

GeminiGoogle

SQL for data analysis - essential queries and techniques

Okay, let's break down essential SQL queries and techniques for data analysis. I'll cover fundamental concepts, common functions, and then tie it all together with practical examples.

I. Core SQL Concepts for Data Analysis

SELECT: The foundation. Used to choose which columns you want to retrieve.

FROM: Specifies the table (or tables) you're querying.

WHERE: Filters rows based on conditions. This is crucial for focusing your analysis.

GROUP BY: Groups rows that have the same values in one or more columns. Essential for aggregations.

HAVING: Filters groups after they've been formed by GROUP BY. It's like WHERE for groups.

ORDER BY: Sorts the result set. Useful for ranking, identifying top/bottom performers, and clearer presentation.

JOIN: Combines data from multiple tables based on related columns. Absolutely vital when your data is spread across multiple tables.

Subqueries: A query nested inside another query. Allows you to perform complex filtering or calculations.

Common Table Expressions (CTEs): A named temporary result set that can be referenced within a larger query. Improves readability and modularity.

Window Functions: Perform calculations across a set of table rows that are related to the current row. Very powerful for ranking, moving averages, and other complex calculations.

II. Fundamental Queries & Techniques

Basic Selection and Filtering:

-- Select all columns from the 'customers' table
    SELECT *
    FROM customers;
    -- Select specific columns (customer_id, first_name, last_name)
    SELECT customer_id, first_name, last_name
    FROM customers;
    -- Filter customers based on their city
    SELECT *
    FROM customers
    WHERE city = 'New York';
    -- Filter customers based on a range of ages
    SELECT *
    FROM customers
    WHERE age BETWEEN 25 AND 35;
    -- Filter customers based on a list of cities
    SELECT *
    FROM customers
    WHERE city IN ('London', 'Paris', 'Tokyo');
    -- Filter customers where the last name starts with 'S'
    SELECT *
    FROM customers
    WHERE last_name LIKE 'S%';  -- % is a wildcard for zero or more characters
    -- Filter customers where the last name contains 'son'
    SELECT *
    FROM customers
    WHERE last_name LIKE '%son%';
    -- Find customers where the email is null
    SELECT *
    FROM customers
    WHERE email IS NULL;    -- Find customers where the email is NOT null
    SELECT *
    FROM customers
    WHERE email IS NOT NULL;

Aggregation and Grouping:

-- Count the total number of customers
    SELECT COUNT(*) AS total_customers
    FROM customers;
    -- Calculate the average age of customers
    SELECT AVG(age) AS average_age
    FROM customers;
    -- Find the maximum and minimum ages
    SELECT MAX(age) AS max_age, MIN(age) AS min_age
    FROM customers;
    -- Sum the total order amounts
    SELECT SUM(order_amount) AS total_order_amount
    FROM orders;
    -- Group customers by city and count the number of customers in each city
    SELECT city, COUNT(*) AS customer_count
    FROM customers
    GROUP BY city;
    -- Group by city and find the average age in each city
    SELECT city, AVG(age) AS average_age
    FROM customers
    GROUP BY city;
    -- Group by city and find the total order amount from customers in each city
    SELECT c.city, SUM(o.order_amount) AS total_order_amount
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.city;    -- Group by city and count customers, but only include cities with more than 5 customers
    SELECT city, COUNT(*) AS customer_count
    FROM customers
    GROUP BY city
    HAVING COUNT(*) > 5;  -- HAVING filters the *groups*

Sorting:

-- Sort customers by last name in ascending order (default)
    SELECT *
    FROM customers
    ORDER BY last_name;
    -- Sort customers by age in descending order
    SELECT *
    FROM customers
    ORDER BY age DESC;    -- Sort by city, then by last name within each city
    SELECT *
    FROM customers
    ORDER BY city, last_name;

Joining Tables:

-- Inner Join:  Get order information along with customer details
    SELECT o.order_id, c.first_name, c.last_name, o.order_date, o.order_amount
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id;
    -- Left Join: Get all customers and their order information (if any)
    SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.order_amount
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id;
    -- Right Join: Get all orders and the customer information (if any)
    SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.order_amount
    FROM customers c
    RIGHT JOIN orders o ON c.customer_id = o.customer_id;    -- Full Outer Join: Get all customers and all orders, regardless of matches
    -- Note:  Not supported directly in MySQL.  Requires a workaround using UNION.
    SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.order_amount
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    UNION ALL
    SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.order_amount
    FROM customers c
    RIGHT JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.customer_id IS NULL;  -- Avoid duplicate rows from the LEFT JOIN part

Subqueries:

-- Find customers who placed orders with an amount greater than the average order amount
    SELECT c.first_name, c.last_name
    FROM customers c
    WHERE c.customer_id IN (
        SELECT o.customer_id
        FROM orders o
        WHERE o.order_amount > (SELECT AVG(order_amount) FROM orders)
    );    -- Get the customer who placed the largest order
    SELECT c.first_name, c.last_name
    FROM customers c
    WHERE c.customer_id = (
        SELECT o.customer_id
        FROM orders o
        ORDER BY o.order_amount DESC
        LIMIT 1
    );

Common Table Expressions (CTEs):

-- Calculate the average order amount per customer WITH CustomerOrderAverages AS ( SELECT customer_id, AVG(order_amount) AS average_order_amount FROM orders GROUP BY customer_id ) SELECT c.first_name, c.last_name, coa.average_order_amount FROM customers c JOIN CustomerOrderAverages coa ON c.customer_id = coa.customer_id;

-- Example with multiple CTEs to calculate customer lifetime value WITH FirstOrders AS ( SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id ), LastOrders AS ( SELECT customer_id, MAX(order_date) AS last_order_date FROM orders GROUP BY customer_id ), CustomerLifetimes AS ( SELECT fo.customer_id, JULIANDAY(lo.last_order_date) - JULIANDAY(fo.first_order_date) AS lifetime_days FROM FirstOrders fo JOIN LastOrders lo ON fo.customer_id = lo.customer_id ) SELECT c.first_name, c.last_name, cl.lifetime_days FROM customers c JOIN CustomerLifetimes cl ON c.customer_id = cl.customer_id;

Window Functions:

-- Rank customers based on their total order amount SELECT c.first_name, c.last_name, SUM(o.order_amount) AS total_order_amount, RANK() OVER (ORDER BY SUM(o.order_amount) DESC) AS order_rank FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name; -- Calculate a moving average of order amounts over the past 3 orders for each customer SELECT o.order_id, o.customer_id, o.order_date, o.order_amount, AVG(o.order_amount) OVER (PARTITION BY o.customer_id ORDER BY o.order_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average FROM orders o;

-- Get the previous order amount for each customer SELECT o.order_id, o.customer_id, o.order_date, o.order_amount, LAG(o.order_amount, 1, 0) OVER (PARTITION BY o.customer_id ORDER BY o.order_date ASC) AS previous_order_amount FROM orders o;

III. Useful SQL Functions for Data Analysis

String Functions:

UPPER(string): Converts a string to uppercase.

LOWER(string): Converts a string to lowercase.

SUBSTRING(string, start, length): Extracts a substring from a string.

LENGTH(string): Returns the length of a string.

TRIM(string): Removes leading and trailing whitespace from a string.

REPLACE(string, oldsubstring, newsubstring): Replaces occurrences of a substring within a string.

CONCAT(string1, string2, ...): Concatenates strings.

Numeric Functions:

ABS(number): Returns the absolute value of a number.

ROUND(number, decimals): Rounds a number to a specified number of decimal places.

FLOOR(number): Returns the largest integer less than or equal to a number.

CEILING(number): Returns the smallest integer greater than or equal to a number.

MOD(number1, number2): Returns the remainder of a division.

Date/Time Functions:

NOW() (or CURRENT_TIMESTAMP): Returns the current date and time.

CURDATE() (or CURRENT_DATE): Returns the current date.

CURTIME() (or CURRENT_TIME): Returns the current time.

DATE(datetime): Extracts the date part from a datetime value.

TIME(datetime): Extracts the time part from a datetime value.

YEAR(date), MONTH(date), DAY(date), HOUR(datetime), MINUTE(datetime), SECOND(datetime): Extract specific date/time parts.

DATE_ADD(date, INTERVAL value unit): Adds an interval to a date.

DATE_SUB(date, INTERVAL value unit): Subtracts an interval from a date.

DATEDIFF(date1, date2): Returns the number of days between two dates.

Aggregate Functions:

COUNT(*): Counts the number of rows.

COUNT(column): Counts the number of non-NULL values in a column.

SUM(column): Sums the values in a column.

AVG(column): Calculates the average of the values in a column.

MIN(column): Finds the minimum value in a column.

MAX(column): Finds the maximum value in a column.

STDDEV(column): Calculates the standard deviation of values in a column.

VARIANCE(column): Calculates the variance of values in a column.

Control Flow Functions:

CASE WHEN condition THEN result ELSE result END: Conditional logic.

IF(condition, valueiftrue, valueiffalse): A shorthand for CASE WHEN.

COALESCE(value1, value2, ...): Returns the first non-NULL value in a list.

IV. Practical Examples & Scenarios

Let's illustrate with a scenario:

Scenario: You work for an e-commerce company and have these tables:

Customers: customerid, firstname, lastname, email, city, signupdate

Orders: orderid, customerid, orderdate, totalamount

Products: productid, productname, category, price

OrderItems: orderid, product_id, quantity

Example Queries:

Top 5 customers by total spending:

SELECT
        c.first_name,
        c.last_name,
        SUM(o.total_amount) AS total_spent
    FROM
        Customers c
    JOIN
        Orders o ON c.customer_id = o.customer_id
    GROUP BY
        c.customer_id, c.first_name, c.last_name
    ORDER BY
        total_spent DESC
    LIMIT 5;

Average order value per month:

SELECT
        YEAR(order_date) AS order_year,
        MONTH(order_date) AS order_month,
        AVG(total_amount) AS average_order_value
    FROM
        Orders
    GROUP BY
        YEAR(order_date), MONTH(order_date)
    ORDER BY
        order_year, order_month;

Most popular product category:

SELECT
        p.category,
        SUM(oi.quantity) AS total_quantity_sold
    FROM
        Products p
    JOIN
        Order_Items oi ON p.product_id = oi.product_id
    GROUP BY
        p.category
    ORDER BY
        total_quantity_sold DESC
    LIMIT 1;

Customers who signed up in the last 30 days and placed an order:

SELECT
        c.first_name,
        c.last_name
    FROM
        Customers c
    WHERE
        c.signup_date >= DATE('now', '-30 days')
        AND c.customer_id IN (SELECT customer_id FROM Orders);

Customer retention rate (percentage of customers who made a purchase in both the current month and the previous month):

WITH
    MonthlyCustomers AS (
        SELECT
            YEAR(order_date) AS order_year,
            MONTH(order_date) AS order_month,
            COUNT(DISTINCT customer_id) AS num_customers
        FROM
            Orders
        GROUP BY
            YEAR(order_date), MONTH(order_date)
    ),
    LaggedMonthlyCustomers AS (
        SELECT
            order_year,
            order_month,
            num_customers,
            LAG(num_customers, 1, 0) OVER (ORDER BY order_year, order_month) AS previous_month_customers
        FROM
            MonthlyCustomers
    ),
    RetainedCustomers AS (
        SELECT
            YEAR(o1.order_date) AS order_year,
            MONTH(o1.order_date) AS order_month,
            COUNT(DISTINCT o1.customer_id) AS num_retained_customers
        FROM
            Orders o1
        JOIN
            Orders o2 ON o1.customer_id = o2.customer_id
            AND YEAR(o1.order_date) = YEAR(DATE(o2.order_date, '+1 month'))
            AND MONTH(o1.order_date) = MONTH(DATE(o2.order_date, '+1 month'))
        GROUP BY
            YEAR(o1.order_date), MONTH(o1.order_date)
    )
    SELECT
        lmc.order_year,
        lmc.order_month,
        lmc.num_customers,
        lmc.previous_month_customers,
        rc.num_retained_customers,
        CAST(rc.num_retained_customers AS REAL) / lmc.previous_month_customers AS retention_rate
    FROM
        LaggedMonthlyCustomers lmc
    JOIN
        RetainedCustomers rc ON lmc.order_year = rc.order_year AND lmc.order_month = rc.order_month;

Key Considerations for Data Analysis in SQL:

Data Types: Be aware of your data types (integer, float, date, string) and use the appropriate functions. Incorrect data types can lead to unexpected results or errors.

NULL Values: Understand how NULL values are handled in SQL. They often require special handling in comparisons and calculations (e.g., using IS NULL, IS NOT NULL, COALESCE).

Performance: For large datasets, performance matters.

Use indexes on columns frequently used in WHERE clauses and JOIN conditions.

Avoid using SELECT * when you only need a few columns.

Be mindful of complex joins, as they can be expensive.

Profile your queries to identify bottlenecks and optimize them.

Database-Specific Syntax: While the core SQL standards are widely followed, there can be slight variations in syntax and function names between different database systems (MySQL, PostgreSQL, SQL Server, Oracle, etc.). Consult your database's documentation.

Data Cleaning: Real-world data is often messy. You might need to use string functions to clean and standardize data before analysis.

Business Context: Always keep the business context in mind. Understand what questions you're trying to answer and what insights you're looking for.

In Summary:

SQL is an essential skill for data analysis. By mastering the core concepts, functions, and techniques covered here, you'll be well-equipped to extract valuable insights from your data. Remember to practice, experiment, and always consult your database documentation for specific details. Good luck!

22:46

Analysis

This comparison demonstrates the different approaches each AI model takes when responding to the same prompt. Here are the key differences observed:

Response Characteristics

Claude: Provides a detailed response with 11 sentences.

DeepSeek: Provides a direct response with 94 sentences.

Gemini: Provides a direct response with 227 sentences.

Key Takeaways

Each model brings unique strengths to this type of query
Response styles vary significantly between models
Consider your specific use case when choosing between these models

Try This Comparison Yourself

Want to test these models with your own prompts? Visit SNEOS.com to compare AI responses side-by-side in real-time.

This comparison was generated using the SNEOS AI Comparison ToolPublished: September 30, 2025 | Models: Claude, DeepSeek, Gemini

Claude vs DeepSeek vs Gemini: SQL Tutorial

SQL for Data Analysis - Essential Queries and Techniques

Table of Contents

Basic Analysis Queries

Data Exploration

Basic Statistics

Aggregation and Grouping

GROUP BY with Multiple Dimensions

HAVING Clause for Filtered Aggregations

Window Functions

Ranking and Row Numbers

1. The Absolute Fundamentals (The "Must-Knows")

a) SELECT & FROM

b) WHERE

c) Aggregate Functions & GROUP BY

d) HAVING

e) ORDER BY

f) JOINs

2. Essential Techniques for Analysis

a) Using CASE for Conditional Logic

b) Date and Time Functions

3. Advanced Analytical Queries (Window Functions)

a) ROWNUMBER(), RANK(), DENSERANK()

b) Running Totals with SUM()

c) LAG() and LEAD()

4. Structuring Complex Queries

a) Common Table Expressions (CTEs)

Best Practices for Data Analysts

Analysis

Response Characteristics

Key Takeaways

Try This Comparison Yourself