Navigation
KSQL

Recipes

Working examples organized by what you're trying to do. Adapt the column and table names to your workspace.

Basic queries

-- All customers, ordered alphabetically
SELECT * FROM Customers ORDER BY Name

-- Active customers with revenue over 50k
SELECT Name, Email, Revenue
FROM Customers
WHERE Status = 'active' AND Revenue > 50000
ORDER BY Revenue DESC

-- Search by pattern
SELECT * FROM Products WHERE Name LIKE '%wireless%'

-- Customers missing email
SELECT Name, Phone FROM Customers WHERE Email IS NULL

-- Top 10 by revenue
SELECT Name, Revenue FROM Customers
ORDER BY Revenue DESC
LIMIT 10

Aggregation and reporting

-- Revenue breakdown by status
SELECT Status,
    COUNT(*)   AS count,
    SUM(Revenue) AS total,
    AVG(Revenue) AS average
FROM Customers
GROUP BY Status
ORDER BY total DESC

-- Monthly order volume
SELECT YEAR(OrderDate)  AS yr,
       MONTH(OrderDate) AS mo,
       COUNT(*)         AS orders,
       SUM(Amount)      AS revenue
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY yr DESC, mo DESC

-- Customers with more than 10 orders
SELECT Customer,
       COUNT(*)    AS order_count,
       SUM(Amount) AS total
FROM Orders
GROUP BY Customer
HAVING COUNT(*) > 10
ORDER BY total DESC

-- Day-of-week analysis
SELECT DAYNAME(CreatedAt) AS day, COUNT(*) AS tickets
FROM SupportTickets
GROUP BY DAYNAME(CreatedAt)
ORDER BY COUNT(*) DESC

Derived fields and segmentation

-- Customer tier based on revenue
SELECT Name,
    CASE
        WHEN Revenue > 100000 THEN 'Enterprise'
        WHEN Revenue > 50000  THEN 'Mid-Market'
        WHEN Revenue > 10000  THEN 'SMB'
        ELSE 'Starter'
    END AS tier,
    Revenue
FROM Customers
ORDER BY Revenue DESC

-- Contact priority fallback
SELECT Name, COALESCE(Phone, Email, Website, 'No contact info') AS primary_contact
FROM Customers

-- Profit margin calculation
SELECT Name,
    Revenue,
    Costs,
    Revenue - Costs AS profit,
    ROUND((Revenue - Costs) * 100.0 / Revenue, 1) AS margin_pct
FROM Deals
WHERE Revenue > 0
ORDER BY margin_pct DESC

-- Display name + initials
SELECT CONCAT(FirstName, ' ', LastName) AS full_name,
    UPPER(LEFT(FirstName, 1)) || UPPER(LEFT(LastName, 1)) AS initials
FROM Employees

-- Conditional status with countdown
SELECT Name,
    IF(DueDate < NOW(), 'OVERDUE', 'On track') AS status,
    DATEDIFF(DueDate, NOW()) AS days_remaining
FROM Tasks
WHERE Status != 'done'

Cross-table queries (joins)

-- Orders with customer details
SELECT c.Name AS customer,
       o.Amount,
       o.Status,
       DATE_FORMAT(o.OrderDate, '%Y-%m-%d') AS date
FROM Orders o
LEFT JOIN Customers c ON o.Customer = c.Name
ORDER BY o.OrderDate DESC

-- Customer order summary
SELECT c.Name,
       COUNT(*)    AS orders,
       SUM(o.Amount) AS total,
       AVG(o.Amount) AS avg_order
FROM Orders o
INNER JOIN Customers c ON o.Customer = c.Name
GROUP BY c.Name
HAVING COUNT(*) > 1
ORDER BY total DESC

-- Products with inventory status
SELECT p.Name, p.Price, i.Quantity,
    CASE
      WHEN i.Quantity = 0  THEN 'Out of Stock'
      WHEN i.Quantity < 10 THEN 'Low Stock'
      ELSE 'In Stock'
    END AS availability
FROM Products p
LEFT JOIN Inventory i ON p.SKU = i.SKU

-- Multi-table join
SELECT e.Name AS employee,
       d.Name AS department,
       m.Name AS manager
FROM Employees e
LEFT JOIN Departments d ON e.Department = d.Name
LEFT JOIN Employees   m ON e.ManagerId = m.Id

Date filtering

-- Last 7 days
SELECT * FROM Orders WHERE CreatedAt > LOOKBACK(7, 'days')

-- Last 3 months
SELECT * FROM Events WHERE EventDate > LOOKBACK(3, 'months')

-- This year
SELECT * FROM Revenue WHERE FiscalDate >= DATE('2024-01-01')

-- Between specific dates
SELECT * FROM Bookings
WHERE CheckIn BETWEEN DATE('2024-06-01') AND DATE('2024-08-31')

-- Group by month with readable names
SELECT MONTHNAME(OrderDate) AS month,
       YEAR(OrderDate)      AS year,
       SUM(Amount)          AS revenue
FROM Orders
WHERE OrderDate > LOOKBACK(12, 'months')
GROUP BY YEAR(OrderDate), MONTH(OrderDate), MONTHNAME(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

-- Weekly trends
SELECT DATE_FORMAT(DATE_TRUNC('week', CreatedAt), '%Y-%m-%d') AS week_start,
       COUNT(*) AS tickets
FROM SupportTickets
GROUP BY DATE_TRUNC('week', CreatedAt)
ORDER BY week_start DESC
LIMIT 12

Tags and multi-select

-- High-priority bugs
SELECT * FROM Issues
WHERE Type = 'bug' AND Labels CONTAINS 'p1'

-- Issues with any frontend label
SELECT * FROM Issues
WHERE Labels CONTAINS ANY ('frontend', 'ui', 'css', 'react')

-- Fully reviewed and tested items
SELECT * FROM Tasks
WHERE Tags CONTAINS ALL ('reviewed', 'tested', 'approved')

-- Items with exactly these two tags
SELECT * FROM Releases
WHERE Labels CONTAINS ONLY ('stable', 'production')

-- Exclude noise
SELECT * FROM Tickets
WHERE Tags NOT CONTAINS 'duplicate'
  AND Tags NOT CONTAINS 'wontfix'
ORDER BY CreatedAt DESC

Next