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
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;Last updated
Was this helpful?