Select Query With Rdms

Breakdown of `SelectQueryParams` Parameters

import {selectSQL} from "agile-query"

distinct (boolean, optional)
- Description: Whether to apply the DISTINCT keyword to the SELECT query, which ensures that only unique rows are returned.
- Example: true (returns distinct rows)
table (string, optional)
- Description: The primary table to select from.
- Example: 'users'
sort (Record<string, 1 | -1> | string | { [P in Tables[number]]?: Record<string, 1 | -1> }, optional)
- Description: Defines the sorting order of the result set. The key can either be the column name or the table name. The value specifies the direction (1 for ascending, -1 for descending).
- Example:
  - { name: 1 } (sort by name in ascending order)
  - { users: { age: -1 } } (sort age in descending order for the users table)
limitSkip ({ limit?: number; skip?: number }, optional)
- Description: Provides pagination options. limit specifies the maximum number of rows to return, and skip specifies how many rows to skip (offset).
- Example: { limit: 10, skip: 20 } (return rows 21 to 30)
columns ({ [P in Tables[number]]?: string[] } | { extra?: string | string[] } | string | string[], optional)
- Description: Specifies the columns to select. It can be a single column name, an array of column names, or a mapping from table names to column arrays.
- Example:
  - 'name' (select only the name column)
  - ['name', 'email'] (select multiple columns)
  - { users: ['name', 'email'], orders: ['id'] } (select columns from multiple tables)
groupBy ({ [P in Tables[number]]?: string[] } | { extra?: string | string[] } | string | string[], optional)
- Description: Defines the columns for grouping the result set. Works similarly to GROUP BY in SQL.
- Example:
  - ['age'] (group by age)
  - { users: ['country'], orders: ['status'] } (group by country in the users table and status in the orders table)
aggregates (Array<{ [K in keyof Record<AggregateFunctions, string>]?: string; } | { alias?: string; }> , optional)
- Description: Defines aggregate functions like COUNT, SUM, AVG, etc., to be applied to columns. Each aggregate function is typically mapped to a column, and you can optionally specify an alias for the result.
- Example:
  - { COUNT: 'id' } (count the number of rows in id)
  - { AVG: 'salary', alias: 'average_salary' } (calculate the average salary and alias it as average_salary)
where (string, optional)
- Description: The WHERE clause condition used to filter the rows based on specific criteria.
- Example: 'age > 30' (select rows where age is greater than 30)
having (string, optional)
- Description: The HAVING clause condition used to filter groups after aggregation.
- Example: 'COUNT(*) > 2' (only select groups having more than 2 rows)
subQueries ({ query: string, as?: string }[], optional)
- Description: Allows the inclusion of subqueries within the main SELECT query. A subquery can be aliased using the as field.
- Example:
```
subQueries: [
  { query: 'SELECT MAX(age) FROM users', as: 'max_age' }
]
```
joins (Array<{ on?: string, operator?: OperatorType | string, type?: 'JOIN' | 'INNER JOIN' | 'OUTER JOIN' | 'CROSS JOIN' | 'RIGHT JOIN' | 'LEFT JOIN'; } | { [P in Tables[number]]?: string; }>, optional)
- Description: Defines the joins to be applied to the query. Each object may contain a type for the join type (e.g., INNER JOIN), an on condition, and an operator to define how tables are linked. This can be used to join multiple tables.
- Example:

     joins: [
       { type: 'INNER JOIN', table: 'orders', on: 'users.id = orders.user_id' }
     ]

   joins: [                                 // JOIN operations to filter rows for deletion
       {
           type: 'INNER JOIN',
           operator: '=', // DEFAULT '='
           customer: 'customer_id', // First table name is primary table 
           order: 'customer_id', // Second table name with column name is foreign table and foreign key
       },
   ]

recursiveCTE ({ baseCase: string, recursiveCase: string, alias: string }, optional)
- Description: Specifies a recursive Common Table Expression (CTE). This is useful for queries that involve hierarchical or recursive data (e.g., organizational charts, bill-of-materials).
- Example:
```
recursiveCTE: {
  baseCase: 'SELECT id, parent_id FROM categories WHERE parent_id IS NULL',
  recursiveCase: 'SELECT c.id, c.parent_id FROM categories c JOIN recursive_cte r ON r.id = c.parent_id',
  alias: 'recursive_cte'
}
```

Example Usage of `SelectQueryParams`

const queryParams: SelectQueryParams<['users', 'orders']> = {
    distinct: true,
    table: 'users',
    columns: { 
        users: ['name', 'email'], 
        orders: ['order_date', 'total'] 
    },
    where: 'age > 30',
    joins: [
        { type: 'INNER JOIN', table: 'orders', on: 'users.id = orders.user_id' }
    ],
    sort: { users: { name: 1 } },
    limitSkip: { limit: 10, skip: 20 },
    aggregates: [{ COUNT: 'id', alias: 'user_count' }],
    groupBy: { users: ['country'] },
    subQueries: [
        { query: 'SELECT MAX(age) FROM users', as: 'max_age' }
    ]
};

In this example:

The query selects name and email from the users table, along with order_date and total from the orders table.
The results are filtered by age > 30.
It includes an INNER JOIN to the orders table.
Sorting is done by name in ascending order.
Limits the result to 10 rows, skipping the first 20.
It calculates the count of users (COUNT(id)) with an alias user_count.
The results are grouped by country from the users table.
A subquery is included to fetch the maximum age from the users table.

Example Usage

Basic Query

const query = selectSQL({
    table: 'users',
    columns: ['id', 'name', 'email'],
    where: 'active = 1',
    sort: { email: 1 },
    limitSkip: { limit: 10 },
});
console.log(query);
// Result:
// SELECT id, name, email FROM users WHERE active = 1 ORDER BY email ASC LIMIT 10;

Query with Joins and Aggregates

const query = selectSQL({
    table: 'orders',
    columns: { orders: ['id', 'customer_id'] },
    joins: [
        { type: 'INNER JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
    ],
    aggregates: [
        { SUM: 'total_price', alias: 'total_sales' },
        { COUNT: 'id', alias: 'order_count' }
    ],
    groupBy: { orders: ['customer_id'] },
    having: 'SUM(total_price) > 1000',
});
console.log(query);
// Result:
// SELECT id, customer_id, SUM(total_price) AS total_sales, COUNT(id) AS order_count 
// FROM orders 
// INNER JOIN customers ON orders.customer_id = customers.id 
// GROUP BY customer_id HAVING SUM(total_price) > 1000;

Query with Recursive CTE

const query = selectSQL({
    recursiveCTE: {
        baseCase: 'SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL',
        recursiveCase: 'SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id',
        alias: 'category_tree',
    },
    columns: ['id', 'name'],
});
console.log(query);
// Result:
// WITH RECURSIVE category_tree AS (
//   SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
//   UNION ALL
//   SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id
// ) SELECT id, name FROM category_tree;

Enhancements and Suggestions

Error Handling: Add validations to detect invalid configurations (e.g., missing table, conflicting clauses).
Dynamic Table Aliases: Allow aliases for tables to simplify complex joins.
Advanced Sorting: Add support for multiple columns in the ORDER BY clause.
SQL Injection Prevention: Ensure proper sanitization or parameterization for user-provided values in where or other clauses.

This query builder is flexible and suitable for most SQL query requirements, offering developers a powerful and structured way to generate SQL dynamically.

Here are 35 SQL query examples, from beginner to advanced, that showcase various possibilities supported by the provided query builder:

Beginner Level

Select All Rows

selectSQL({ table: 'users' });
// SELECT * FROM users;

Select Specific Columns

selectSQL({ table: 'users', columns: ['id', 'name'] });
// SELECT id, name FROM users;

Add a WHERE Clause

selectSQL({ table: 'users', where: 'age > 18' });
// SELECT * FROM users WHERE age > 18;

Add Sorting

selectSQL({ table: 'users', sort: { name: 1 } });
// SELECT * FROM users ORDER BY name ASC;

Apply DISTINCT

selectSQL({ table: 'users', distinct: true });
// SELECT DISTINCT * FROM users;

Apply LIMIT and OFFSET

selectSQL({ table: 'users', limitSkip: { limit: 5, skip: 10 } });
// SELECT * FROM users LIMIT 5 OFFSET 10;

Intermediate Level

Group Results

selectSQL({
    table: 'sales',
    groupBy: ['region'],
    aggregates: [{ SUM: 'amount', alias: 'total_sales' }],
});
// SELECT SUM(amount) AS total_sales FROM sales GROUP BY region;

Filter with HAVING

selectSQL({
    table: 'sales',
    groupBy: ['region'],
    aggregates: [{ SUM: 'amount', alias: 'total_sales' }],
    having: 'SUM(amount) > 1000',
});
// SELECT SUM(amount) AS total_sales FROM sales GROUP BY region HAVING SUM(amount) > 1000;

Join Two Tables

selectSQL({
    table: 'orders',     
    joins: [
        { type: 'INNER JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
    ],
    columns: ['orders.id', 'customers.name'],
});
// SELECT orders.id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

Perform Aggregations

    selectSQL({
        table: 'products',
        aggregates: [{ COUNT: '*', alias: 'total_products' }],
    });
    // SELECT COUNT(*) AS total_products FROM products;

Use Multiple Aggregates

    selectSQL({
        table: 'sales',
        aggregates: [
            { SUM: 'amount', alias: 'total_amount' },
            { AVG: 'amount', alias: 'average_amount' },
        ],
    });
    // SELECT SUM(amount) AS total_amount, AVG(amount) AS average_amount FROM sales;

Multiple WHERE Conditions

    selectSQL({ table: 'users', where: 'age > 18 AND active = 1' });
    // SELECT * FROM users WHERE age > 18 AND active = 1;

Advanced Level

Recursive CTE

    selectSQL({
        recursiveCTE: {
            baseCase: 'SELECT id, parent_id FROM categories WHERE parent_id IS NULL',
            recursiveCase: 'SELECT c.id, c.parent_id FROM categories c INNER JOIN tree t ON c.parent_id = t.id',
            alias: 'tree',
        },
        columns: ['id', 'parent_id'],
    });
    // WITH RECURSIVE tree AS (
    //   SELECT id, parent_id FROM categories WHERE parent_id IS NULL
    //   UNION ALL
    //   SELECT c.id, c.parent_id FROM categories c INNER JOIN tree t ON c.parent_id = t.id
    // ) SELECT id, parent_id FROM tree;

Join with Aggregates

    selectSQL({
        table: 'orders',
        joins: [
            { type: 'LEFT JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
        ],
        aggregates: [{ COUNT: 'orders.id', alias: 'order_count' }],
        groupBy: ['customers.id'],
    });
    // SELECT COUNT(orders.id) AS order_count 
    // FROM orders LEFT JOIN customers ON orders.customer_id = customers.id 
    // GROUP BY customers.id;

Subqueries

    selectSQL({
        table: 'products',
        subQueries: [
            { query: 'SELECT MAX(price) FROM products', as: 'max_price' },
        ],
        columns: ['id', 'name'],
    });
    // SELECT id, name, (SELECT MAX(price) FROM products) AS max_price FROM products;

Using Aggregates and Joins

    selectSQL({
        table: 'sales',
        joins: [{ type: 'INNER JOIN', on: 'sales.product_id = products.id', table: 'products' }],
        aggregates: [{ SUM: 'sales.amount', alias: 'total_sales' }],
        groupBy: ['products.category_id'],
    });
    // SELECT SUM(sales.amount) AS total_sales 
    // FROM sales INNER JOIN products ON sales.product_id = products.id 
    // GROUP BY products.category_id;

Nested Aggregates

    selectSQL({
        table: 'orders',
        groupBy: ['customer_id'],
        aggregates: [{ COUNT: '*', alias: 'order_count' }],
        having: 'COUNT(*) > (SELECT AVG(order_count) FROM orders GROUP BY customer_id)',
    });
    // SELECT COUNT(*) AS order_count 
    // FROM orders GROUP BY customer_id 
    // HAVING COUNT(*) > (SELECT AVG(order_count) FROM orders GROUP BY customer_id);

Multiple Joins

    selectSQL({
        table: 'orders',
        joins: [
            { type: 'INNER JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
            { type: 'LEFT JOIN', on: 'orders.product_id = products.id', table: 'products' },
        ],
        columns: ['orders.id', 'customers.name', 'products.name'],
    });
    // SELECT orders.id, customers.name, products.name 
    // FROM orders 
    // INNER JOIN customers ON orders.customer_id = customers.id 
    // LEFT JOIN products ON orders.product_id = products.id;

Advanced Examples with Complex Queries

Combined Aggregates and Subqueries

    selectSQL({
        table: 'orders',
        aggregates: [
            { SUM: 'total', alias: 'total_sales' },
            { AVG: 'total', alias: 'average_order' },
        ],
        subQueries: [{ query: 'SELECT COUNT(*) FROM customers', as: 'customer_count' }],
    });
    // SELECT SUM(total) AS total_sales, AVG(total) AS average_order, (SELECT COUNT(*) FROM customers) AS customer_count 
    // FROM orders;

Dynamic WHERE and Joins

    selectSQL({
        table: 'orders',
        where: 'status = "completed"',
        joins: [
            { type: 'INNER JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
        ],
    });
    // SELECT * FROM orders 
    // INNER JOIN customers ON orders.customer_id = customers.id 
    // WHERE status = "completed";

21. Filtering by a Date Range

selectSQL({
    table: 'orders',
    where: 'order_date BETWEEN "2024-01-01" AND "2024-12-31"',
    columns: ['id', 'customer_id', 'order_date'],
});
// SELECT id, customer_id, order_date 
// FROM orders 
// WHERE order_date BETWEEN "2024-01-01" AND "2024-12-31";

22. Filtering with `IN` Operator

selectSQL({
    table: 'products',
    where: 'category_id IN (1, 2, 3)',
    columns: ['id', 'name', 'category_id'],
});
// SELECT id, name, category_id 
// FROM products 
// WHERE category_id IN (1, 2, 3);

23. Joining More Than Three Tables

selectSQL({
    table: 'orders',
    joins: [
        { type: 'INNER JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
        { type: 'LEFT JOIN', on: 'orders.product_id = products.id', table: 'products' },
        { type: 'RIGHT JOIN', on: 'products.supplier_id = suppliers.id', table: 'suppliers' },
    ],
    columns: ['orders.id', 'customers.name', 'products.name', 'suppliers.name'],
});
// SELECT orders.id, customers.name, products.name, suppliers.name 
// FROM orders 
// INNER JOIN customers ON orders.customer_id = customers.id 
// LEFT JOIN products ON orders.product_id = products.id 
// RIGHT JOIN suppliers ON products.supplier_id = suppliers.id;

24. Combining `UNION` in Subqueries

selectSQL({
    table: 'employees',
    subQueries: [
        { query: 'SELECT id, name FROM employees WHERE department_id = 1', as: 'sales_team' },
        { query: 'SELECT id, name FROM employees WHERE department_id = 2', as: 'support_team' },
    ],
});
// SELECT (SELECT id, name FROM employees WHERE department_id = 1) AS sales_team, 
// (SELECT id, name FROM employees WHERE department_id = 2) AS support_team 
// FROM employees;

25. Performing Window Functions (e.g., Ranking)

(Note: Extend the builder to handle window functions)

selectSQL({
    table: 'employees',
    columns: [
        'id', 
        'name', 
        { query: 'ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)', as: 'rank' },
    ],
});
// SELECT id, name, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank 
// FROM employees;

26. Aggregating with a Custom Alias Logic

selectSQL({
    table: 'sales',
    aggregates: [
        { SUM: 'amount', alias: 'total_revenue' },
        { MAX: 'amount', alias: 'highest_sale' },
        { MIN: 'amount', alias: 'lowest_sale' },
    ],
});
// SELECT SUM(amount) AS total_revenue, MAX(amount) AS highest_sale, MIN(amount) AS lowest_sale 
// FROM sales;

27. Joining with Multiple Conditions

selectSQL({
    table: 'orders',
    joins: [
        {
            type: 'INNER JOIN',
            on: 'orders.customer_id = customers.id AND orders.status = "completed"',
            table: 'customers',
        },
    ],
    columns: ['orders.id', 'customers.name'],
});
// SELECT orders.id, customers.name 
// FROM orders 
// INNER JOIN customers ON orders.customer_id = customers.id AND orders.status = "completed";

28. Including Raw SQL in `WHERE` or Joins

selectSQL({
    table: 'products',
    where: 'price > (SELECT AVG(price) FROM products)',
    columns: ['id', 'name', 'price'],
});
// SELECT id, name, price 
// FROM products 
// WHERE price > (SELECT AVG(price) FROM products);

29. Using Multiple `HAVING` Conditions

selectSQL({
    table: 'sales',
    groupBy: ['region'],
    aggregates: [{ SUM: 'amount', alias: 'total_sales' }],
    having: 'SUM(amount) > 1000 AND COUNT(*) > 10',
});
// SELECT SUM(amount) AS total_sales 
// FROM sales 
// GROUP BY region 
// HAVING SUM(amount) > 1000 AND COUNT(*) > 10;

30. Recursive Hierarchy with Aggregates

selectSQL({
    recursiveCTE: {
        baseCase: 'SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL',
        recursiveCase: 'SELECT c.id, c.parent_id, c.name FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id',
        alias: 'category_tree',
    },
    aggregates: [{ COUNT: 'id', alias: 'total_categories' }],
    groupBy: ['parent_id'],
});
// WITH RECURSIVE category_tree AS (
//   SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
//   UNION ALL
//   SELECT c.id, c.parent_id, c.name FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id
// )
// SELECT COUNT(id) AS total_categories 
// FROM category_tree 
// GROUP BY parent_id;

31. Using Dynamic Table Aliases

selectSQL({
    table: 'employees AS e',
    columns: ['e.id', 'e.name', 'e.department_id'],
    where: 'e.salary > 50000',
});
// SELECT e.id, e.name, e.department_id 
// FROM employees AS e 
// WHERE e.salary > 50000;

32. Filtering with LIKE

selectSQL({
    table: 'customers',
    where: 'name LIKE "John%"',
    columns: ['id', 'name'],
});
// SELECT id, name 
// FROM customers 
// WHERE name LIKE "John%";

33. Using Boolean Operators

selectSQL({
    table: 'users',
    where: '(age > 18 AND active = 1) OR role = "admin"',
    columns: ['id', 'name', 'role'],
});
// SELECT id, name, role 
// FROM users 
// WHERE (age > 18 AND active = 1) OR role = "admin";

34. Subqueries in the WHERE Clause

selectSQL({
    table: 'employees',
    where: 'department_id = (SELECT id FROM departments WHERE name = "HR")',
    columns: ['id', 'name'],
});
// SELECT id, name 
// FROM employees 
// WHERE department_id = (SELECT id FROM departments WHERE name = "HR");

35. Combining Joins and Subqueries

selectSQL({
    table: 'orders',
    joins: [
        { type: 'INNER JOIN', on: 'orders.customer_id = (SELECT id FROM customers WHERE vip_status = 1)', table: 'customers' },
    ],
    columns: ['orders.id', 'customers.name'],
});
// SELECT orders.id, customers.name 
// FROM orders 
// INNER JOIN customers ON orders.customer_id = (SELECT id FROM customers WHERE vip_status = 1);

Here are examples 35 to 40 that tackle even more complex SQL scenarios and use cases:

35. Recursive CTE for a Tree Structure with Aggregates

selectSQL({
    recursiveCTE: {
        baseCase: 'SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL',
        recursiveCase: 'SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id',
        alias: 'category_tree',
    },
    columns: ['id', 'name', 'parent_id'],
    aggregates: [{ COUNT: 'id', alias: 'total_nodes' }],
    groupBy: ['parent_id'],
    having: 'COUNT(id) > 5',
});
// WITH RECURSIVE category_tree AS (
//   SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
//   UNION ALL
//   SELECT c.id, c.name, c.parent_id FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id
// )
// SELECT id, name, parent_id, COUNT(id) AS total_nodes 
// FROM category_tree 
// GROUP BY parent_id 
// HAVING COUNT(id) > 5;

36. Complex Subqueries with Conditional Aggregates

selectSQL({
    table: 'employees',
    columns: [
        'id',
        'name',
        { query: '(SELECT COUNT(*) FROM projects WHERE projects.employee_id = employees.id)', as: 'total_projects' },
        { query: 'IF(salary > 50000, "high", "low")', as: 'salary_category' },
    ],
    where: 'active = 1',
    sort: { salary: -1 },
});
// SELECT id, name, 
//   (SELECT COUNT(*) FROM projects WHERE projects.employee_id = employees.id) AS total_projects,
//   IF(salary > 50000, "high", "low") AS salary_category 
// FROM employees 
// WHERE active = 1 
// ORDER BY salary DESC;

37. Multi-Level Joins with Filters

selectSQL({
    table: 'orders',
    joins: [
        { type: 'INNER JOIN', on: 'orders.customer_id = customers.id', table: 'customers' },
        { type: 'INNER JOIN', on: 'customers.region_id = regions.id', table: 'regions' },
        { type: 'LEFT JOIN', on: 'orders.product_id = products.id', table: 'products' },
    ],
    columns: ['orders.id', 'customers.name', 'regions.name AS region', 'products.name AS product'],
    where: 'regions.name = "North America" AND products.price > 100',
});
// SELECT orders.id, customers.name, regions.name AS region, products.name AS product 
// FROM orders 
// INNER JOIN customers ON orders.customer_id = customers.id 
// INNER JOIN regions ON customers.region_id = regions.id 
// LEFT JOIN products ON orders.product_id = products.id 
// WHERE regions.name = "North America" AND products.price > 100;

38. Combining Multiple CTEs and Aggregates

selectSQL({
    recursiveCTE: {
        baseCase: 'SELECT id, name, parent_id FROM employees WHERE parent_id IS NULL',
        recursiveCase: 'SELECT e.id, e.name, e.parent_id FROM employees e INNER JOIN employee_hierarchy eh ON e.parent_id = eh.id',
        alias: 'employee_hierarchy',
    },
    subQueries: [
        { query: 'SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id', as: 'department_summary' },
    ],
    columns: [
        'employee_hierarchy.id',
        'employee_hierarchy.name',
        { query: 'department_summary.dept_count', as: 'total_department_employees' },
    ],
    joins: [{ type: 'INNER JOIN', on: 'employee_hierarchy.id = department_summary.department_id', table: 'department_summary' }],
});
// WITH RECURSIVE employee_hierarchy AS (
//   SELECT id, name, parent_id FROM employees WHERE parent_id IS NULL
//   UNION ALL
//   SELECT e.id, e.name, e.parent_id FROM employees e INNER JOIN employee_hierarchy eh ON e.parent_id = eh.id
// ),
// department_summary AS (
//   SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id
// )
// SELECT employee_hierarchy.id, employee_hierarchy.name, department_summary.dept_count AS total_department_employees 
// FROM employee_hierarchy 
// INNER JOIN department_summary ON employee_hierarchy.id = department_summary.department_id;

39. Advanced Window Function with Filters

selectSQL({
    table: 'sales',
    columns: [
        'id',
        'region',
        { query: 'RANK() OVER (PARTITION BY region ORDER BY total_sales DESC)', as: 'rank' },
        { query: 'SUM(total_sales) OVER (PARTITION BY region)', as: 'region_sales' },
    ],
    where: 'total_sales > 1000',
});
// SELECT id, region, 
//   RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS rank, 
//   SUM(total_sales) OVER (PARTITION BY region) AS region_sales 
// FROM sales 
// WHERE total_sales > 1000;

40. Combining Analytical Functions with Aggregates

selectSQL({
    table: 'sales',
    columns: [
        'region',
        { query: 'SUM(amount)', as: 'total_sales' },
        { query: 'AVG(amount)', as: 'avg_sales' },
        { query: 'RANK() OVER (ORDER BY SUM(amount) DESC)', as: 'sales_rank' },
    ],
    groupBy: ['region'],
    having: 'SUM(amount) > 10000',
    sort: { 'total_sales': -1 },
});
// SELECT region, 
//   SUM(amount) AS total_sales, 
//   AVG(amount) AS avg_sales, 
//   RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank 
// FROM sales 
// GROUP BY region 
// HAVING SUM(amount) > 10000 
// ORDER BY total_sales DESC;