Mixed Queries

String Operations

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

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.

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.

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.

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.

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

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

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

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

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

pepsi Table

-- 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);

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;