SQL Basics and Intermediate

Chapter 1

To install DB Browser for SQLite using Homebrew, follow these steps:

Installation Steps

1. Update Homebrew (optional but recommended):

   Copy

   brew update

2. Install DB Browser for SQLite:

   Copy

   brew install --cask db-browser-for-sqlite

3. Verify Installation: Once installed, you can open the app from your Applications folder or by typing:

   Copy

   open -a "DB Browser for SQLite"

About DB Browser for SQLite

DB Browser for SQLite is a visual tool to help you interact with SQLite databases. It allows you to:

• Create, edit, and browse SQLite databases.

• Run SQL queries in a user-friendly interface.

• Export and import data easily.

Let me know if you encounter any issues!

---

Chapter 2

Here’s how you can perform basic CRUD operations in SQLite directly from the terminal:

---

Step 1: Open SQLite

Run the SQLite command-line interface:

Copy

sqlite3 example.db

This creates or opens the database file example.db.

---

Step 2: Create a Table

Execute the following SQL command to create a table:

Copy

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
);

---

Step 3: Insert Data (CREATE)

Insert a record into the table:

Copy

INSERT INTO users (name, age, email)
VALUES ('Alice', 30, '[e-mail]');

---

Step 4: Read Data (READ)

Retrieve all data from the table:

Copy

SELECT * FROM users;

Output:

Copy

1|Alice|30|[e-mail]

---

Step 5: Update Data (UPDATE)

Modify a record in the table:

Copy

UPDATE users
SET age = 31
WHERE name = 'Alice';

Verify the change:

Copy

SELECT * FROM users;

---

Step 6: Delete Data (DELETE)

Remove a record from the table:

Copy

DELETE FROM users
WHERE name = 'Alice';

Verify the deletion:

Copy

SELECT * FROM users;

---

Step 7: Exit SQLite

Type the following to exit the SQLite shell:

Copy

.exit

---

Summary of Commands

1. Start SQLite: sqlite3 example.db

2. Create Table:

   Copy

   CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, email TEXT UNIQUE);

3. Insert Data:

   Copy

   INSERT INTO users (name, age, email) VALUES ('Alice', 30, '[e-mail]');

4. Read Data:

   Copy

   SELECT * FROM users;

5. Update Data:

   Copy

   UPDATE users SET age = 31 WHERE name = 'Alice';

6. Delete Data:

   Copy

   DELETE FROM users WHERE name = 'Alice';

7. Exit: .exit

You’ve now performed CRUD operations in SQLite directly from the terminal!

---

Chapter 3

To install DB Browser for SQLite using Homebrew, follow these steps:

Installation Steps

1. Update Homebrew (optional but recommended):

   Copy

   brew update

2. Install DB Browser for SQLite:

   Copy

   brew install --cask db-browser-for-sqlite

3. Verify Installation: Once installed, you can open the app from your Applications folder or by typing:

   Copy

   open -a "DB Browser for SQLite"

About DB Browser for SQLite

DB Browser for SQLite is a visual tool to help you interact with SQLite databases. It allows you to:

• Create, edit, and browse SQLite databases.

• Run SQL queries in a user-friendly interface.

• Export and import data easily.

---

Chapter 4

Here are 20 SQL commands with slightly more complex operations that you can try in SQLite:

---

1. Add a New Column to an Existing Table

Copy

ALTER TABLE users ADD COLUMN phone TEXT;

---

2. Insert Multiple Rows

Copy

INSERT INTO users (name, age, email) VALUES 
('Bob', 25, '[e-mail]'),
('Charlie', 28, '[e-mail]'),
('Diana', 22, '[e-mail]');

---

3. Filter Rows with WHERE and AND

Copy

SELECT * FROM users WHERE age > 25 AND email LIKE '%example.com';

---

4. Sort Results with ORDER BY

Copy

SELECT * FROM users ORDER BY age DESC;

---

5. Limit Results with LIMIT

Copy

SELECT * FROM users ORDER BY age DESC LIMIT 2;

---

6. Find Rows with Missing Data (NULL)

Copy

SELECT * FROM users WHERE phone IS NULL;

---

7. Update Multiple Columns

Copy

UPDATE users SET phone = '123-456-7890', age = 29 WHERE name = 'Charlie';

---

8. Delete Rows Matching a Condition

Copy

DELETE FROM users WHERE age < 23;

---

9. Rename a Table

Copy

ALTER TABLE users RENAME TO user_data;

---

10. Group Data with GROUP BY

Copy

SELECT age, COUNT(*) as count FROM users GROUP BY age;

---

11. Filter Groups with HAVING

Copy

SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 1;

---

12. Calculate Aggregate Functions

Copy

SELECT AVG(age) as average_age, MAX(age) as oldest, MIN(age) as youngest FROM users;

---

13. Use Subqueries

Copy

SELECT * FROM users WHERE age = (SELECT MAX(age) FROM users);

---

14. Join Two Tables (INNER JOIN)

Copy

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    total REAL
);

INSERT INTO orders (user_id, total) VALUES (1, 99.99), (2, 49.99);

SELECT users.name, orders.total 
FROM users
INNER JOIN orders ON users.id = orders.user_id;

---

15. Left Join to Include Unmatched Rows

Copy

SELECT users.name, orders.total 
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

---

16. Count Rows

Copy

SELECT COUNT(*) as user_count FROM users;

---

17. Find Unique Values with DISTINCT

Copy

SELECT DISTINCT age FROM users;

---

18. Create a View

Copy

CREATE VIEW user_summary AS
SELECT name, age, email FROM users WHERE age > 25;

Query the view:

Copy

SELECT * FROM user_summary;

---

19. Use a CASE Statement

Copy

SELECT name, 
       CASE 
           WHEN age < 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 60 THEN 'Adult'
           ELSE 'Senior'
       END AS age_group
FROM users;

---

20. Drop a Table

Copy

DROP TABLE orders;

---

These commands are a mix of data manipulation, querying, and database structure management, providing you with a deeper understanding of SQL operations.

---

Chapter 5

Here are 20 more advanced SQL commands that showcase complex queries and operations in SQLite. These commands include advanced joins, subqueries, window functions, and more:

---

1. Self Join

Copy

SELECT a.name AS user1, b.name AS user2
FROM users a
INNER JOIN users b ON a.age = b.age AND a.id != b.id;

---

2. Find Duplicate Records

Copy

SELECT name, COUNT(*) as count
FROM users
GROUP BY name
HAVING count > 1;

---

3. Recursive Query for Hierarchical Data

Copy

WITH RECURSIVE hierarchy(id, name, level) AS (
    SELECT id, name, 1 FROM users WHERE id = 1
    UNION ALL
    SELECT u.id, u.name, h.level + 1
    FROM users u
    JOIN hierarchy h ON u.id = h.id + 1
)
SELECT * FROM hierarchy;

---

4. Correlated Subquery

Copy

SELECT name, age
FROM users u
WHERE age > (SELECT AVG(age) FROM users WHERE id != u.id);

---

5. Window Function for Ranking

Copy

SELECT name, age, 
       RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

---

6. Cumulative Sum with Window Function

Copy

SELECT name, age,
       SUM(age) OVER (ORDER BY age) AS cumulative_age
FROM users;

---

7. Find Users with Maximum Orders

Copy

SELECT name
FROM users
WHERE id = (SELECT user_id FROM orders GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1);

---

8. Delete Duplicate Rows (Keep One Record)

Copy

DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY name, age, email
);

---

9. Find the Second Highest Age

Copy

SELECT MAX(age) AS second_highest_age
FROM users
WHERE age < (SELECT MAX(age) FROM users);

---

10. Find Users with No Orders

Copy

SELECT name
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

---

11. Update Using a Subquery

Copy

UPDATE users
SET age = age + 1
WHERE id IN (SELECT id FROM users WHERE age < 30);

---

12. Pivot Table Using Conditional Aggregation

Copy

SELECT 
    name,
    SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) AS below_20,
    SUM(CASE WHEN age BETWEEN 20 AND 40 THEN 1 ELSE 0 END) AS between_20_40
FROM users
GROUP BY name;

---

13. Join Three Tables

Copy

SELECT u.name, o.total, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.id = p.order_id;

---

14. Find the Most Common Age

Copy

SELECT age, COUNT(*) as frequency
FROM users
GROUP BY age
ORDER BY frequency DESC
LIMIT 1;

---

15. Aggregate with Multiple Grouping Levels

Copy

SELECT age, COUNT(*) as count
FROM users
GROUP BY age
WITH ROLLUP;

---

16. Insert Data from Another Table

Copy

INSERT INTO archived_users (id, name, age, email)
SELECT id, name, age, email
FROM users
WHERE age > 50;

---

17. Find Users Who Made Orders Above a Certain Total

Copy

SELECT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

---

18. Full Outer Join Simulation

Copy

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

---

19. JSON Data Query

Copy

CREATE TABLE json_data (id INTEGER PRIMARY KEY, data JSON);

INSERT INTO json_data (data) VALUES ('{"name": "Alice", "age": 30}');
SELECT json_extract(data, '$.name') AS name, json_extract(data, '$.age') AS age FROM json_data;

---

20. Create a Trigger

Copy

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

---

These commands provide a range of advanced SQL techniques, including recursive queries, window functions, triggers, and JSON operations. They are ideal for deepening your knowledge of SQL and mastering complex use cases.

---

Chapter 6

Here are 20 more complex SQL commands to expand your knowledge further. These cover advanced techniques like CTEs, indexing, transactions, and more:

---

1. Find the Top 3 Ages

Copy

SELECT DISTINCT age
FROM users
ORDER BY age DESC
LIMIT 3;

---

2. Conditional Aggregation

Copy

SELECT 
    COUNT(CASE WHEN age < 20 THEN 1 END) AS under_20,
    COUNT(CASE WHEN age BETWEEN 20 AND 30 THEN 1 END) AS between_20_30,
    COUNT(CASE WHEN age > 30 THEN 1 END) AS over_30
FROM users;

---

3. Find All Users with the Same Age as Others

Copy

SELECT name, age
FROM users
WHERE age IN (
    SELECT age
    FROM users
    GROUP BY age
    HAVING COUNT(*) > 1
);

---

4. Row Number Using Window Function

Copy

SELECT name, age, 
       ROW_NUMBER() OVER (PARTITION BY age ORDER BY name) AS row_num
FROM users;

---

5. Dense Rank Using Window Function

Copy

SELECT name, age, 
       DENSE_RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

---

6. NTILE Using Window Function

Copy

SELECT name, age, 
       NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

---

7. Common Table Expression (CTE) for Summing Ages

Copy

WITH AgeSums AS (
    SELECT age, COUNT(*) AS count
    FROM users
    GROUP BY age
)
SELECT * FROM AgeSums WHERE count > 1;

---

8. Generate Sequential Numbers

Copy

WITH RECURSIVE numbers(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1
    FROM numbers
    WHERE x < 10
)
SELECT x FROM numbers;

---

9. Composite Primary Key

Copy

CREATE TABLE user_orders (
    user_id INTEGER,
    order_id INTEGER,
    PRIMARY KEY (user_id, order_id)
);

---

10. Create an Index

Copy

CREATE INDEX idx_age ON users(age);

---

11. Using Transactions

Copy

BEGIN TRANSACTION;
INSERT INTO users (name, age, email) VALUES ('Eve', 35, '[e-mail]');
UPDATE users SET age = 36 WHERE name = 'Eve';
ROLLBACK; -- Use COMMIT to save changes

---

12. Find Users Without Orders Using EXISTS

Copy

SELECT name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

---

13. Calculate Percentage

Copy

SELECT name, 
       age,
       ROUND((age * 100.0 / (SELECT MAX(age) FROM users)), 2) AS age_percentage
FROM users;

---

14. Dynamic Group By with CTE

Copy

WITH AgeGroups AS (
    SELECT name, 
           CASE 
               WHEN age < 20 THEN 'Under 20'
               WHEN age BETWEEN 20 AND 30 THEN '20-30'
               ELSE 'Above 30'
           END AS age_group
    FROM users
)
SELECT age_group, COUNT(*) AS count
FROM AgeGroups
GROUP BY age_group;

---

15. Find Gaps in a Sequence

Copy

SELECT x + 1 AS missing_number
FROM (SELECT DISTINCT age AS x FROM users) a
WHERE NOT EXISTS (
    SELECT 1 FROM users b WHERE b.age = a.x + 1
);

---

16. Create a Stored Procedure Equivalent with Triggers

Copy

CREATE TRIGGER log_user_insertion
AFTER INSERT ON users
BEGIN
    INSERT INTO logs (event, created_at) VALUES ('New user added', CURRENT_TIMESTAMP);
END;

---

17. Update Using Joins

Copy

UPDATE users
SET age = (SELECT MAX(o.total) FROM orders o WHERE o.user_id = users.id)
WHERE id IN (SELECT user_id FROM orders);

---

18. Generate Running Totals

Copy

SELECT name, age, 
       SUM(age) OVER (ORDER BY name) AS running_total
FROM users;

---

19. Delete Rows with Limit

SQLite doesn’t support DELETE ... LIMIT, but you can emulate it:

Copy

DELETE FROM users
WHERE id IN (
    SELECT id FROM users ORDER BY age LIMIT 2
);

---

20. Check Query Execution Plan

Copy

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE age > 30;

---

These commands showcase advanced SQL concepts like window functions, recursive queries, indexing, transactions, and triggers. They are ideal for diving deeper into SQL's capabilities! Let me know if you need clarification on any of them.

---

Chapter 7

Here are 20 more advanced and complex SQL queries covering scenarios like advanced joins, analytical functions, hierarchical data, and dynamic queries.

---

1. Find Consecutive Records

Copy

SELECT a.id, a.name, a.age, b.age AS next_age
FROM users a
JOIN users b ON a.id = b.id - 1;

---

2. Rank Users by Age and Handle Ties

Copy

SELECT name, age, 
       RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

---

3. Calculate Rolling Average

Copy

SELECT name, age, 
       AVG(age) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM users;

---

4. Generate Sequential Dates

Copy

WITH RECURSIVE date_range(start_date) AS (
    SELECT DATE('2024-01-01')
    UNION ALL
    SELECT DATE(start_date, '+1 day')
    FROM date_range
    WHERE start_date < DATE('2024-01-10')
)
SELECT * FROM date_range;

---

5. Find the Longest Consecutive Sequence

Copy

WITH consecutive AS (
    SELECT age, 
           age - ROW_NUMBER() OVER (ORDER BY age) AS group_id
    FROM users
)
SELECT MIN(age) AS start_age, MAX(age) AS end_age, COUNT(*) AS length
FROM consecutive
GROUP BY group_id
ORDER BY length DESC
LIMIT 1;

---

6. Identify Overlapping Date Ranges

Copy

SELECT a.id, b.id
FROM reservations a
JOIN reservations b 
ON a.start_date < b.end_date AND a.end_date > b.start_date AND a.id != b.id;

---

7. Combine Two Columns into One

Copy

SELECT id, name || ' (' || email || ')' AS combined
FROM users;

---

8. Aggregate JSON Data

Copy

SELECT json_group_array(json_object('name', name, 'age', age)) AS json_output
FROM users;

---

9. Find the Nth Highest Value

Copy

SELECT DISTINCT age
FROM users
ORDER BY age DESC
LIMIT 1 OFFSET 2; -- Finds the 3rd highest value

---

10. Check if Two Tables Have the Same Data

Copy

SELECT COUNT(*) AS mismatches
FROM (
    SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2
) AS diff;

---

11. Dynamic Pivot Table

Copy

SELECT 
    name, 
    SUM(CASE WHEN age < 20 THEN 1 ELSE 0 END) AS under_20,
    SUM(CASE WHEN age BETWEEN 20 AND 30 THEN 1 ELSE 0 END) AS between_20_30,
    SUM(CASE WHEN age > 30 THEN 1 ELSE 0 END) AS over_30
FROM users
GROUP BY name;

---

12. Find Rows with Greatest N Per Group

Copy

WITH ranked AS (
    SELECT name, age, 
           RANK() OVER (PARTITION BY name ORDER BY age DESC) AS rank
    FROM users
)
SELECT name, age
FROM ranked
WHERE rank <= 3;

---

13. Split Strings into Rows

Copy

WITH split(value, rest) AS (
    SELECT '', my_string || ',' FROM (SELECT 'a,b,c,d' AS my_string)
    UNION ALL
    SELECT substr(rest, 0, instr(rest, ',')), substr(rest, instr(rest, ',') + 1)
    FROM split
    WHERE rest != ''
)
SELECT value FROM split;

---

14. Recursive Fibonacci Sequence

Copy

WITH RECURSIVE fib(a, b) AS (
    SELECT 0, 1
    UNION ALL
    SELECT b, a + b FROM fib WHERE a + b < 100
)
SELECT a AS fibonacci FROM fib;

---

15. Calculate Percentile

Copy

SELECT age,
       PERCENT_RANK() OVER (ORDER BY age) AS percentile
FROM users;

---

16. Union with Deduplication

Copy

SELECT * FROM users
UNION
SELECT * FROM archived_users;

---

17. Detect Data Drift

Copy

SELECT column_name, COUNT(*) AS frequency
FROM pragma_table_info('users')
GROUP BY column_name
HAVING frequency > 1;

---

18. Simulate Full Outer Join

Copy

SELECT u.id, u.name, o.order_total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.id, u.name, o.order_total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

---

19. Cohort Analysis

Copy

WITH cohort AS (
    SELECT id, 
           DATE(join_date, 'start of month') AS cohort_month
    FROM users
)
SELECT cohort_month, COUNT(*) AS user_count
FROM cohort
GROUP BY cohort_month;

---

20. Find Users With Matching Initials

Copy

SELECT a.name AS user1, b.name AS user2
FROM users a
JOIN users b 
ON substr(a.name, 1, 1) = substr(b.name, 1, 1)
AND a.id != b.id;

---

These examples cover a variety of advanced SQL use cases, including recursive queries, dynamic pivots, advanced joins, and analytical functions.

---

Chapter 8

Here are 20 more complex SQL queries that dive even deeper into advanced SQL concepts, such as data manipulation, window functions, advanced subqueries, and dynamic operations.

---

1. Find Users Who Placed Orders on Every Day of a Month

Copy

SELECT user_id
FROM orders
WHERE strftime('%Y-%m', order_date) = '2024-11'
GROUP BY user_id
HAVING COUNT(DISTINCT strftime('%Y-%m-%d', order_date)) = (
    SELECT COUNT(DISTINCT strftime('%Y-%m-%d', order_date))
    FROM orders
    WHERE strftime('%Y-%m', order_date) = '2024-11'
);

---

2. Calculate Median Age

Copy

SELECT AVG(age) AS median_age
FROM (
    SELECT age
    FROM users
    ORDER BY age
    LIMIT 2 - (SELECT COUNT(*) % 2 FROM users)
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM users)
);

---

3. Cumulative Percentage

Copy

SELECT name, age,
       ROUND(SUM(age) OVER (ORDER BY age) * 100.0 / SUM(age) OVER (), 2) AS cumulative_percentage
FROM users;

---

4. Find the Largest Gap in Ages

Copy

SELECT MAX(age - prev_age) AS largest_gap
FROM (
    SELECT age, LAG(age) OVER (ORDER BY age) AS prev_age
    FROM users
) AS gaps
WHERE prev_age IS NOT NULL;

---

5. List All Pairs of Users With the Same Age

Copy

SELECT a.name AS user1, b.name AS user2
FROM users a
JOIN users b ON a.age = b.age AND a.id < b.id;

---

6. Find Users with More Than One Email

Copy

SELECT user_id
FROM emails
GROUP BY user_id
HAVING COUNT(*) > 1;

---

7. Find the Maximum Order Amount for Each User

Copy

SELECT user_id, MAX(total_amount) AS max_order
FROM orders
GROUP BY user_id;

---

8. Filter Outliers Using the Interquartile Range

Copy

WITH stats AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY age) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY age) AS q3
    FROM users
)
SELECT *
FROM users
WHERE age BETWEEN (SELECT q1 - 1.5 * (q3 - q1) FROM stats) AND (SELECT q3 + 1.5 * (q3 - q1) FROM stats);

---

9. Rank Users by Total Order Amount

Copy

SELECT user_id, 
       SUM(total_amount) AS total_spent,
       RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rank
FROM orders
GROUP BY user_id;

---

10. Find Consecutive Days with Orders

Copy

WITH consecutive_days AS (
    SELECT user_id, order_date,
           JULIANDAY(order_date) - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS group_id
    FROM orders
)
SELECT user_id, MIN(order_date) AS start_date, MAX(order_date) AS end_date
FROM consecutive_days
GROUP BY user_id, group_id
HAVING COUNT(*) > 1;

---

11. Find Users Who Have Not Ordered for the Past 30 Days

Copy

SELECT name
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
    WHERE order_date >= DATE('now', '-30 days')
);

---

12. Find Top Spending Users by Month

Copy

SELECT strftime('%Y-%m', order_date) AS month, user_id,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY month, user_id
ORDER BY month, total_spent DESC;

---

13. Find Users Who Share the Same First Name

Copy

SELECT a.name AS name1, b.name AS name2
FROM users a
JOIN users b ON substr(a.name, 1, instr(a.name, ' ') - 1) = substr(b.name, 1, instr(b.name, ' ') - 1)
WHERE a.id < b.id;

---

14. Check for Referential Integrity Violations

Copy

SELECT *
FROM orders
WHERE user_id NOT IN (SELECT id FROM users);

---

15. Update a Column Based on a Condition

Copy

UPDATE users
SET phone = 'Not Provided'
WHERE phone IS NULL;

---

16. Find Orders Where the Total Amount Exceeds the User’s Average

Copy

SELECT o.order_id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.total_amount > (SELECT AVG(total_amount) FROM orders WHERE user_id = u.id);

---

17. Generate All Possible Pairs of Users

Copy

SELECT a.name AS user1, b.name AS user2
FROM users a
CROSS JOIN users b
WHERE a.id < b.id;

---

18. Find Users Who Made More Than One Order in a Day

Copy

SELECT user_id, order_date, COUNT(*) AS order_count
FROM orders
GROUP BY user_id, order_date
HAVING order_count > 1;

---

19. Calculate Time Difference Between Orders

Copy

SELECT user_id, 
       order_id, 
       LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order_date,
       JULIANDAY(LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date)) - JULIANDAY(order_date) AS days_between
FROM orders;

---

20. Dynamic Query with Aggregates

Copy

SELECT user_id,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS total_spent,
       AVG(total_amount) AS average_order_value
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;

---

These advanced SQL queries cover a wide range of scenarios and techniques, such as handling time-based data, advanced grouping, filtering outliers, and generating dynamic aggregations.

---