SQL Queries That Will Make You Look Like a Database Expert – HackemTU

Most developers learn just enough SQL to get by – SELECT, WHERE, JOIN, maybe GROUP BY. But there’s a whole world of SQL techniques that can save you hours of work and make your queries significantly more powerful.

Índice

Window Functions: The Game Changer

Window functions let you perform calculations across rows without collapsing them (unlike GROUP BY). Once you learn these, you’ll wonder how you ever lived without them.

-- Rank employees by salary within each department
SELECT 
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

This gives you each employee’s rank within their department AND how their salary compares to the department average – all in a single query.

Common Table Expressions (CTEs)

CTEs make complex queries readable. Instead of nesting subqueries three levels deep, you break them into named steps:

WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as total
    FROM orders
    GROUP BY 1
),
growth AS (
    SELECT 
        month, total,
        LAG(total) OVER (ORDER BY month) as prev_month,
        ROUND((total - LAG(total) OVER (ORDER BY month)) / 
              LAG(total) OVER (ORDER BY month) * 100, 2) as growth_pct
    FROM monthly_sales
)
SELECT * FROM growth WHERE growth_pct IS NOT NULL;

COALESCE and NULLIF: Handling Nulls Gracefully

-- Use a default value when data is missing
SELECT 
    name,
    COALESCE(phone, email, 'No contact info') as contact,
    COALESCE(nickname, first_name) as display_name
FROM users;

-- Avoid division by zero
SELECT 
    department,
    total_revenue / NULLIF(employee_count, 0) as revenue_per_employee
FROM departments;

CASE Statements for Business Logic

SELECT 
    order_id,
    amount,
    CASE 
        WHEN amount >= 1000 THEN 'Premium'
        WHEN amount >= 100 THEN 'Standard'
        ELSE 'Basic'
    END as tier,
    CASE 
        WHEN payment_status = 'paid' AND shipped = true THEN 'Complete'
        WHEN payment_status = 'paid' THEN 'Awaiting Shipment'
        ELSE 'Pending Payment'
    END as order_status
FROM orders;

EXISTS vs IN (Performance Matters)

-- EXISTS is usually faster for large subqueries
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id 
    AND o.amount > 500
);

-- IN works fine for small lists
SELECT * FROM products 
WHERE category_id IN (1, 5, 12, 23);

Quick Wins

-- Get distinct values with counts
SELECT status, COUNT(*) as cnt 
FROM orders GROUP BY status ORDER BY cnt DESC;

-- Find duplicates
SELECT email, COUNT(*) FROM users 
GROUP BY email HAVING COUNT(*) > 1;

-- Date ranges made easy
SELECT * FROM events 
WHERE event_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days';

Master these patterns and you’ll handle 90% of real-world SQL tasks with confidence. The key is practice – grab a dataset, open a SQL client, and start querying.