Skip to main content
2026-07-29 16:01
Database

Common Table Expressions (CTE)

QueryValidateExpert
Try Interactive Demo

Description

Use CTEs for complex queries with readable structure.

Test Scenario

-- Your query using CTEs:
WITH order_totals AS (
  SELECT o.id, o.user_id, SUM(oi.quantity * oi.unit_price) as calculated_total
  FROM orders o
  JOIN order_items oi ON o.id = oi.order_id
  GROUP BY o.id
),
user_stats AS (
  SELECT user_id, COUNT(*) as order_count, SUM(calculated_total) as lifetime_value
  FROM order_totals
  GROUP BY user_id
)
SELECT u.name, us.order_count, us.lifetime_value
FROM user_stats us
JOIN users u ON us.user_id = u.id
ORDER BY us.lifetime_value DESC;

-- Expected: User rankings by lifetime value

Testing Tips

  • •WITH clause defines CTE
  • •CTEs can be chained
  • •Improves query readability
  • •Can be recursive for hierarchies
// Playwright API Testing - Database
import { test, expect } from '@playwright/test';


test('Database query via API', async ({ request }) => {
  const response = await request.post('/api/database/query', {
    data: {
      sql: 'SELECT * FROM users WHERE id = ?',
      params: [1]
    }
  });


  expect(response.ok()).toBeTruthy();
  const { rows } = await response.json();
  expect(rows.length).toBeGreaterThan(0);
});

Challenge ID: sql-cte-recursive

2 frameworks available