# Mixed Queries

## String Operations

\
Show the name of the countries where the capital is the country name + 'City' in the WOLRD table.

```sql
SELECT country FROM world
WHERE capital = CONCAT(country,' City')
```

***

Extract all the details with a winner whose name is EUGENE O'NEILL (single quote) from NOBEL.

```sql
SELECT yr, subject, winner
FROM nobel
WHERE winner = 'EUGENE O''NEILL'
```

***

Query the list of CITY names starting with vowels (i.e. a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

```sql
SELECT DISTINCT city
FROM station
WHERE city LIKE 'A%' 
    OR city LIKE 'E%' 
    OR city LIKE 'I%' 
    OR city LIKE 'O%' 
    OR city LIKE 'U%';

-- OR

SELECT DISTINCT city
FROM station
WHERE city ~ '^[AEIOU]'; -- Regex like query

```

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

```sql
SELECT DISTINCT city
FROM station
WHERE NOT (city LIKE 'A%' 
    OR city LIKE 'E%' 
    OR city LIKE 'I%' 
    OR city LIKE 'O%' 
    OR city LIKE 'U%')
AND NOT (city LIKE '%a' 
    OR city LIKE '%e' 
    OR city LIKE '%i' 
    OR city LIKE '%o' 
    OR city LIKE '%u');
    
-- OR

SELECT DISTINCT city
FROM station
WHERE NOT (city ~ '^[AEIOU]') AND NOT (city ~'[aeiou]$');
```

***

Query the name of any student in STUDENTS whose marks are higher than 75. **Order your output by the last three characters of each name.** If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

```sql
SELECT name
FROM students
WHERE marks > 75
ORDER BY SUBSTRING(name,-3), ID ASC 
```

***

Display records of customers who are either from Berlin or Munchen Germany 

```sql
SELECT * FROM customers
WHERE country='Germany' 
AND (City='Berlin' OR City='München');
-- OR
SELECT * FROM customers
WHERE country='Germany' 
AND (City IN ('Berlin' City='München'));
```

## Aggregations / Functions

Return largest populations per continent from WORLD

```sql
SELECT continent, MAX(population) AS max_pop
FROM world
WHERE population != 0
GROUP BY 1
ORDER BY 1;
```

Return the TOP 3 populations per continent

```sql
SELECT *
FROM (
     SELECT 
          continent,
          name, 
          population,
          RANK() OVER (PARTITION BY continent ORDER BY population DESC) AS pop_rank 
     FROM world
     WHERE population <> 0) AS t1
WHERE pop_rank <= 3
ORDER BY continent, pop_rank;

```

***

Find all the users who were active for 3 consecutive days or more

```sql
SELECT DISTINCT user_id
FROM (
    SELECT t1.*,
        lag(date) OVER (PARTITION BY user_id ORDER BY date) as prev_active_date,
        lag(date, 2) OVER (PARTITION BY user_id ORDER BY date) as prev_Active_date2
    FROM (
        SELECT DISTINCT user_id, date
        FROM sf_events 
        ) t1
    ) t2
WHERE prev_active_date = date - interval '1' day 
AND prev_Active_date2 = date - interval '2' day;

-- OR

SELECT DISTINCT s1.user_id
FROM sf_events s1
JOIN sf_events s2
ON s1.user_id = s2.user_id AND s1.date = s2.date - interval '1' day
JOIN sf_events s3
ON s1.user_id = s3.user_id AND s1.date = s3.date - interval '2' day;
```

***

Find Pepsi's Top selling products per day per retailer. Keep in mind one retailer might have ordered the same quantity for different products on a given day

<details>

<summary>pepsi Table</summary>

```sql
-- Create Table
CREATE TABLE IF NOT EXISTS pepsi (
	date DATE,
	retailer VARCHAR(50),
	product VARCHAR(50),
	quantity SMALLINT
);

# Add Data to the Table
INSERT INTO pepsi VALUES ('2022-01-01','Walmart', 'Pepsi',10000);
INSERT INTO pepsi VALUES ('2022-01-01','Target', 'Gatorade',10000);
INSERT INTO pepsi VALUES ('2022-01-01','Costco', 'Pepsi',8000);
INSERT INTO pepsi VALUES ('2022-01-01','Walmart', 'PepsiMax',10000);
INSERT INTO pepsi VALUES ('2022-01-01','ACME', 'Pepsi',4500);
INSERT INTO pepsi VALUES ('2022-01-01','Walmart', 'Aquafina',12500);
INSERT INTO pepsi VALUES ('2022-01-01','Walmart', 'PepsiMax',2500);
INSERT INTO pepsi VALUES ('2022-01-01','Target', 'Naked',5000);
INSERT INTO pepsi VALUES ('2022-01-01','Walgreens', 'PepsiMax',7500);
INSERT INTO pepsi VALUES ('2022-01-01','Walmart', 'Aquafina',2500);
INSERT INTO pepsi VALUES ('2022-01-01','CVS', 'Pepsi',3500);
INSERT INTO pepsi VALUES ('2022-01-01','Walmart', 'PepsiMax',1000);
INSERT INTO pepsi VALUES ('2022-01-02','ACME', 'Pepsi',2000);
INSERT INTO pepsi VALUES ('2022-01-02','Walmart', 'Aquafina',12500);
INSERT INTO pepsi VALUES ('2022-01-02','Walmart', 'PepsiMax',2500);
INSERT INTO pepsi VALUES ('2022-01-02','Target', 'Naked',1000);
INSERT INTO pepsi VALUES ('2022-01-02','Walgreens', 'PepsiMax',7500);
INSERT INTO pepsi VALUES ('2022-01-02','Walmart', 'Aquafina',2500);
INSERT INTO pepsi VALUES ('2022-01-02','ACME', 'Tropicana',2000);
INSERT INTO pepsi VALUES ('2022-01-02','Walmart', 'Aquafina',12500);
INSERT INTO pepsi VALUES ('2022-01-02','CVS', 'PepsiMax',2500);
INSERT INTO pepsi VALUES ('2022-01-03','Costco', 'Pepsi',8000);
INSERT INTO pepsi VALUES ('2022-01-03','Walmart', 'PepsiMax',10000);
INSERT INTO pepsi VALUES ('2022-01-03','ACME', 'Tropicana',4500);
INSERT INTO pepsi VALUES ('2022-01-03','Walmart', 'Aquafina',12500);
INSERT INTO pepsi VALUES ('2022-01-03','Walmart', 'PepsiMax',2500);
INSERT INTO pepsi VALUES ('2022-01-03','Target', 'Naked',5000);
INSERT INTO pepsi VALUES ('2022-01-03','Walgreens', 'PepsiMax',7500);
INSERT INTO pepsi VALUES ('2022-01-03','Walmart', 'Aquafina',2500);
INSERT INTO pepsi VALUES ('2022-01-03','CVS', 'Pepsi',3500);
INSERT INTO pepsi VALUES ('2022-01-03','Costo', 'PepsiMax',1000);

```

</details>

```sql
SELECT
	tmp.*
FROM (
	SELECT *, 
		DENSE_RANK() OVER (PARTITION BY date,retailer ORDER BY quantity DESC) as qty_rank
	FROM pepsi
) AS tmp
WHERE qty_rank=1;
```