SQL
SQL (Structured Query Language) is the standard language for relational databases. Every data engineer, backend developer, and data scientist needs solid SQL skills.
SQL's declarative nature - stating what you want rather than how to get it - leaves optimization to the query planner. Writing efficient SQL requires understanding how the planner interprets your query, where indexes help, and which operations are expensive.
Building data warehouses and analytics systems at Lorex and FLIR Systems revealed that SQL performance directly impacts business decisions. A slow dashboard query might delay critical decisions by hours. Query optimization, understanding execution plans, and smart indexing became high-impact skills.
Window functions are the most powerful and underused feature in standard SQL. RANK, LAG, LEAD, SUM OVER, and ROW_NUMBER unlock cohort analysis, running totals, time-series comparisons, and deduplication patterns that would otherwise require multiple subqueries or application-side processing.
Schema design is a long-term commitment. Normalization decisions made at schema creation time determine query complexity, write performance, and data integrity for the entire lifetime of the application. Spending extra time on entity-relationship modeling, index strategy, and constraint design prevents years of expensive workarounds and data inconsistencies.
Contents 4 Sections
1Core SQL
-- SELECT
SELECT first_name, last_name, email
FROM users
WHERE active = true
AND created_at > '2024-01-01'
ORDER BY last_name ASC, first_name ASC
LIMIT 50 OFFSET 100;-- Aggregate functions SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, SUM(salary) AS total_payroll FROM employees GROUP BY department HAVING COUNT(*) > 5 ORDER BY avg_salary DESC;
-- INSERT INSERT INTO orders (user_id, product_id, quantity, total) VALUES (123, 456, 2, 99.98);
-- UPDATE UPDATE products SET price = price * 1.1, updated_at = NOW() WHERE category = 'electronics' AND in_stock = true;
-- DELETE DELETE FROM sessions WHERE expires_at < NOW(); ```
2JOINs
-- INNER JOIN only matching rows
SELECT o.id, u.name, p.title, o.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id;-- LEFT JOIN all left rows, NULL if no match SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;
-- Self join join table to itself SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
-- CROSS JOIN cartesian product SELECT s.size, c.color FROM sizes s CROSS JOIN colors c; ```
3Advanced SQL
-- Window functions
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
LAG(salary) OVER (PARTITION BY department ORDER BY hire_date) AS prev_salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;-- CTEs (Common Table Expressions) WITH monthly_revenue AS ( SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 ), growth AS ( SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM monthly_revenue ) SELECT month, revenue, ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct FROM growth;
-- CASE WHEN SELECT name, salary, CASE WHEN salary > 150000 THEN 'Senior' WHEN salary > 100000 THEN 'Mid' ELSE 'Junior' END AS level FROM employees; ```
4Query Optimization
-- ── EXPLAIN ANALYZE (PostgreSQL) ─────────────────────
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;-- Read output: -- "Seq Scan" = table scan (bad on large tables → add index) -- "Index Scan" = using index (good) -- "Bitmap Index Scan" = using index for range (good) -- "Hash Join" = joining by building hash table -- rows= / width= : estimated rows and row size
-- ── Indexing strategies ─────────────────────────────── -- Single column CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- Composite (order matters leftmost prefix rule) CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- Supports: WHERE user_id = x AND status = y -- Supports: WHERE user_id = x (uses left part) -- Does NOT: WHERE status = y (skips left column)
-- Partial index (smaller, faster) CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';
-- Expression index CREATE INDEX idx_users_lower_email ON users(lower(email)); -- Now: WHERE lower(email) = 'user@example.com' uses index
-- Covering index (avoids table lookup entirely) CREATE INDEX idx_orders_covering ON orders(user_id, created_at) INCLUDE (total, status);
-- ── N+1 Query Problem ───────────────────────────────── -- Bad: separate query per user (N+1) SELECT * FROM users; -- then for each user: SELECT * FROM orders WHERE user_id = ?;
-- Good: single JOIN SELECT u.*, json_agg(o.*) AS orders FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id;
-- ── Partitioning for large tables ───────────────────── CREATE TABLE events ( id BIGSERIAL, user_id BIGINT, event_type TEXT, created_at TIMESTAMPTZ NOT NULL ) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_q1 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2024-04-01'); ```
Explore the Full Portfolio
This is the complete portfolio of Sufi Khan Sulaiman, a technology leader specialising in B2B commerce and digital automation. Start from the Home page for the overview, then move through two decades of career experience across FLIR Systems, Lorex Technology, and 1c Platform, and the full catalogue of project case studies spanning headless commerce migrations, AI recommendation engines, and multi-channel fulfilment systems.
The skills and certifications page maps the technical and leadership capabilities behind the work, while the articles and the knowledge base break down the thinking into actionable frameworks. For hands-on learning, the tutorials and applications sections cover practical builds from front-end fundamentals to full-stack web apps.
For consulting engagement, the expertise page outlines service offerings, the ecommerce hub covers platform architecture and automation strategy, and the ecommerce guide (PDF) is a downloadable 55-page field manual. When you are ready to talk, the contact page is the direct line.