Single Table
1
- Title of all projects whose budget is greater than 20000
- Order by title
┌────────────────────────┐
│ Title │
│ varchar │
├────────────────────────┤
│ Plant Restructurierung │
│ Strategy │
└────────────────────────┘
select title
from project
where budget > 20000
order by title;
2
- Title of all projects that don't have a budget
- Order by title
┌─────────┐
│ Title │
│ varchar │
├─────────┤
│ Sales │
└─────────┘
select title
from project
where budget is null
order by title;
3
- EID and last name of all employees of org unit 17 that have a bonus
- Order by EID
┌───────┬──────────┐
│ EID │ LastName │
│ int32 │ varchar │
├───────┼──────────┤
│ 117 │ Oezdem │
│ 118 │ Okeke │
└───────┴──────────┘
select eid, lastname
from employee
where ouid=17 and not bonus is null
order by eid;
4
- All columns of all employees whose name starts with letter S
- Order by EID
┌───────┬───────┬──────────┬────────────┬──────────────┬──────────────┐
│ EID │ OUID │ LastName │ Hiredate │ Salary │ Bonus │
│ int32 │ int32 │ varchar │ date │ decimal(9,2) │ decimal(9,2) │
├───────┼───────┼──────────┼────────────┼──────────────┼──────────────┤
│ 104 │ 13 │ Stone │ 2006-06-01 │ 42000.00 │ NULL │
│ 108 │ 14 │ Sanchez │ 2014-04-01 │ 39000.00 │ 1500.00 │
│ 112 │ 16 │ Singh │ 2012-03-01 │ 43000.00 │ 2100.00 │
└───────┴───────┴──────────┴────────────┴──────────────┴──────────────┘
select *
from employee
where lastname like 'S%'
order by eid;
5
- Name and duration of all titles that are longer than 400 sec
- Order by duration descending
┌──────────────────┬───────────────┐
│ Name │ DurationInSec │
│ varchar │ int32 │
├──────────────────┼───────────────┤
│ Are We Here? │ 934 │
│ Sad But True │ 469 │
│ Butterfly Caught │ 455 │
│ The World Is Our │ 432 │
└──────────────────┴───────────────┘
select name, durationinsec
from title
where durationinsec > 400
order by durationinsec desc;
6
- Names of all playlists whose last call was between Sep. 16 and Sep. 17, 2025
- Order by name
┌──────────────┬────────────┐
│ Name │ LastCall │
│ varchar │ date │
├──────────────┼────────────┤
│ chillout │ 2025-09-16 │
│ danceout │ 2025-09-17 │
│ hard n heavy │ 2025-09-16 │
└──────────────┴────────────┘
select name, lastcall
from playlist
where lastcall between '2025-09-16' and '2025-09-17'
order by name;
7
- TID, name and duration band of all titles
- Bands:
- durationinsec < 300: short
- 300 <= durationinsec < 400: medium
- 400 <= durationinsec < 800: long
- durationinsec >= 800: very long
- Order by durationinsec
┌───────┬──────────────────┬───────────┐
│ TID │ Name │ band │
│ int32 │ varchar │ varchar │
├───────┼──────────────────┼───────────┤
│ 2 │ I Will Fade │ short │
│ 3 │ Out of Sight │ short │
│ 10 │ Soul for Me │ short │
│ 6 │ Aotearoa │ short │
│ 8 │ Science Friction │ medium │
│ 11 │ Idioteque │ medium │
│ 13 │ Remedies │ medium │
│ 4 │ Gorecki │ medium │
│ 1 │ Sit Back Down │ medium │
│ 12 │ The World Is Our │ long │
│ 5 │ Butterfly Caught │ long │
│ 7 │ Sad But True │ long │
│ 9 │ Are We Here? │ very long │
├───────┴──────────────────┴───────────┤
│ 13 rows 3 columns │
└──────────────────────────────────────┘
select tid, name,
case
when durationinsec < 300 then 'short'
when durationinsec < 400 then 'medium'
when durationinsec < 800 then 'long'
else 'very long'
end as band
from title
order by durationinsec;
8
- Name durationinsec, and duration in minutes:seconds of all titles
- Order by name
┌──────────────────┬───────────────┬──────────┐
│ Name │ DurationInSec │ duration │
│ varchar │ int32 │ varchar │
├──────────────────┼───────────────┼──────────┤
│ Aotearoa │ 266 │ 04:26 │
│ Are We Here? │ 934 │ 15:34 │
│ Butterfly Caught │ 455 │ 07:35 │
│ Gorecki │ 390 │ 06:30 │
│ I Will Fade │ 189 │ 03:09 │
│ Idioteque │ 309 │ 05:09 │
│ Out of Sight │ 236 │ 03:56 │
│ Remedies │ 378 │ 06:18 │
│ Sad But True │ 469 │ 07:49 │
│ Science Friction │ 304 │ 05:04 │
│ Sit Back Down │ 396 │ 06:36 │
│ Soul for Me │ 243 │ 04:03 │
│ The World Is Our │ 432 │ 07:12 │
├──────────────────┴───────────────┴──────────┤
│ 13 rows 3 columns │
└─────────────────────────────────────────────┘
select
name, durationinsec,
format('{:02d}', floor(durationinsec / 60)::int) || ':' ||
format('{:02d}', mod(durationinsec, 60)) as duration
from title
order by name;
Join
1
- All playlists together with all titles on them
- Order by playlist, title
┌──────────────┬──────────────────┐
│ playlist │ title │
│ varchar │ varchar │
├──────────────┼──────────────────┤
│ chillout │ Butterfly Caught │
│ chillout │ I Will Fade │
│ chillout │ Sit Back Down │
│ chillout │ Soul for Me │
│ danceout │ Are We Here? │
│ danceout │ Sad But True │
│ danceout │ Science Friction │
│ epic │ Aotearoa │
│ epic │ Gorecki │
│ epic │ Idioteque │
│ epic │ Out of Sight │
│ hard n heavy │ Remedies │
│ hard n heavy │ The World Is Our │
├──────────────┴──────────────────┤
│ 13 rows 2 columns │
└─────────────────────────────────┘
select pl.name as playlist, t.name as title
from playlist pl
join title t on t.plid=pl.plid
order by playlist, title;
2
- All genre together with all titles belonging to them
- Order by genre, title
┌──────────┬──────────────────┐
│ genre │ title │
│ varchar │ varchar │
├──────────┼──────────────────┤
│ Pop │ I Will Fade │
│ Pop │ Sit Back Down │
│ Rock │ Idioteque │
│ Rock │ Remedies │
│ Rock │ The World Is Our │
│ Trip Hop │ Aotearoa │
│ Trip Hop │ Are We Here? │
│ Trip Hop │ Butterfly Caught │
│ Trip Hop │ Gorecki │
│ Trip Hop │ Out of Sight │
│ Trip Hop │ Sad But True │
│ Trip Hop │ Science Friction │
│ Trip Hop │ Soul for Me │
├──────────┴──────────────────┤
│ 13 rows 2 columns │
└─────────────────────────────┘
select g.description as genre, t.name as title
from genre g
join title t on t.gid=g.gid
order by genre, title;
3
- For each playlist: which genres are on it
- Use distinct
- Order by playlist
┌──────────────┬──────────┐
│ playlist │ genre │
│ varchar │ varchar │
├──────────────┼──────────┤
│ chillout │ Pop │
│ chillout │ Trip Hop │
│ danceout │ Trip Hop │
│ epic │ Trip Hop │
│ epic │ Rock │
│ hard n heavy │ Rock │
└──────────────┴──────────┘
select distinct
pl.name as playlist, g.description as genre
from playlist pl
join title t on t.plid=pl.plid
join genre g on g.gid=t.gid
order by pl.name;
4
- Name of all Bosses
- Order by bossname
┌──────────┐
│ bossname │
│ varchar │
├──────────┤
│ Blaschke │
│ Durmaz │
│ Li │
│ Ortega │
│ Patil │
│ Umarani │
│ Umarani │
└──────────┘
select lastname as bossname
from orgunit ou
join employee e on ou.head=e.eid
order by lastname;
5
- Name of all orgunits that are related to projects, i.e. where at least one employee of that orgunit is involved in a project
- Use distinct
- Order by project, orgunit
┌────────────────────────┬────────────────┐
│ project │ orgunit │
│ varchar │ varchar │
├────────────────────────┼────────────────┤
│ CRM │ Administration │
│ CRM │ HR │
│ Plant Restructurierung │ Production │
│ Plant Restructurierung │ Warehouse │
│ Strategy │ Administration │
│ Strategy │ Company │
│ Strategy │ Production │
└────────────────────────┴────────────────┘
select distinct p.title as project, ou.name as orgunit
from project p
join empproj ep on ep.pid=p.pid
join employee e on e.eid=ep.eid
join orgunit ou on ou.ouid=e.ouid
order by project, orgunit;
6
- Who earns more than his boss
- Order by boss
┌──────────┬─────────┬──────────────┬──────────────┐
│ employee │ boss │ esal │ bsal │
│ varchar │ varchar │ decimal(9,2) │ decimal(9,2) │
├──────────┼─────────┼──────────────┼──────────────┤
│ Jadhav │ Ortega │ 91000.00 │ 90000.00 │
└──────────┴─────────┴──────────────┴──────────────┘
select e.lastname as employee, b.lastname as boss, e.salary as esal, b.salary as bsal
from employee e
join orgunit ou on ou.ouid=e.ouid
join employee b on b.eid=ou.head
where e.salary>b.salary
order by boss;
7
- All genres that are not related to any title
- Order by genre
┌─────────┐
│ genre │
│ varchar │
├─────────┤
│ Folk │
└─────────┘
select g.description as genre
from title t
right join genre g on g.gid=t.gid
where t.tid is null
order by genre;
8
- Project costs per employee
- 40 hours per week
- Project lasts exactly one year
- Only labor costs, no material costs
- Only salary is considered
- Order by
┌────────────────────────┬──────────┬───────┐
│ project │ employee │ cost │
│ varchar │ varchar │ int32 │
├────────────────────────┼──────────┼───────┤
│ CRM │ Blaschke │ 18600 │
│ CRM │ Durmaz │ 12000 │
│ Plant Restructurierung │ Oezdem │ 3300 │
│ Plant Restructurierung │ Okeke │ 6400 │
│ Plant Restructurierung │ Popov │ 3400 │
│ Plant Restructurierung │ Umarani │ 7100 │
│ Strategy │ Durmaz │ 12000 │
│ Strategy │ Patil │ 18000 │
│ Strategy │ Umarani │ 14200 │
└────────────────────────┴──────────┴───────┘
select title as project, lastname as employee, (noofhoursperweek/40*salary)::int as cost
from employee e
join empproj ep on ep.eid=e.eid
join project p on p.pid=ep.pid
order by project, employee;
Aggregation
1
- Total duration and number of records per genre
- Order by genre
┌──────────┬───────────────┬─────────────┐
│ genre │ totalduration │ noofrecords │
│ varchar │ int128 │ int64 │
├──────────┼───────────────┼─────────────┤
│ Pop │ 585 │ 2 │
│ Rock │ 1119 │ 3 │
│ Trip Hop │ 3297 │ 8 │
└──────────┴───────────────┴─────────────┘
SELECT description as genre, sum(DURATIONINSEC) AS totalduration, COUNT(*) AS noofrecords
FROM title t
JOIN genre g ON g.gid=t.gid
GROUP BY description
order by genre;
2
- Number of employees per project
- Only projects with more than 2 members should be in the output
- Order by project
┌────────────────────────┬──────────┐
│ project │ NoOfEmps │
│ varchar │ int64 │
├────────────────────────┼──────────┤
│ Plant Restructurierung │ 4 │
│ Strategy │ 3 │
└────────────────────────┴──────────┘
select title as project, count(*) as NoOfEmps
from project p
join empproj ep on p.pid=ep.pid
group by title
having count(*) > 2
order by project;
3
- Name of orgunit and number of employees
- Only employees whose salary exceeds 59000
- Order by orgunit
┌────────────────┬──────────┐
│ orgunit │ NoOfEmps │
│ varchar │ int64 │
├────────────────┼──────────┤
│ Accounting │ 1 │
│ Administration │ 1 │
│ Company │ 1 │
│ HR │ 1 │
│ Plant │ 2 │
│ Production │ 1 │
└────────────────┴──────────┘
select name as orgunit, count(*) as NoOfEmps
from orgunit ou
join employee e on e.ouid=ou.ouid
where salary > 59000
group by name
order by orgunit;
4
- Cost per project
- 40 hours per week
- Project lasts exactly one year
- Only labor costs, no material costs
- Only salary is considered
- Order by project
┌────────────────────────┬───────┐
│ project │ cost │
│ varchar │ int32 │
├────────────────────────┼───────┤
│ CRM │ 30600 │
│ Plant Restructurierung │ 20200 │
│ Sales │ 0 │
│ Strategy │ 44200 │
└────────────────────────┴───────┘
select p.title as project, coalesce(sum(ep.noofhoursperweek/40*e.salary)::int, 0) as cost
from project p
left join empproj ep on p.pid=ep.pid
left join employee e on e.eid=ep.eid
group by p.title
order by project;
5
- Projects whose cost overrun budget
- 40 hours per week
- Project lasts exactly one year
- Only labor costs, no material costs
- Only salary is considered
- xxx
- Order by project
┌─────────┬───────────────┬───────┐
│ project │ Budget │ cost │
│ varchar │ decimal(13,2) │ int32 │
├─────────┼───────────────┼───────┤
│ CRM │ 20000.00 │ 30600 │
└─────────┴───────────────┴───────┘
select p.title as project, budget, coalesce(sum(ep.noofhoursperweek/40*e.salary)::int, 0) as cost
from project p
left join empproj ep on p.pid=ep.pid
left join employee e on e.eid=ep.eid
group by p.title, p.budget
having sum(ep.noofhoursperweek/40*e.salary) > budget
order by project;
6
- Number of Customers wrt geography hierarchy
- Order by number_of_customers
┌─────────┬──────────┬───────────────┬─────────────────────┐
│ Country │ State │ City │ number_of_customers │
│ varchar │ varchar │ varchar │ int64 │
├─────────┼──────────┼───────────────┼─────────────────────┤
│ Canada │ BC │ Shawnee │ 111 │
│ USA │ CA │ Downey │ 110 │
│ USA │ OR │ Lebanon │ 108 │
│ USA │ CA │ Lemon Grove │ 107 │
│ USA │ CA │ Concord │ 106 │
│ Mexico │ Guerrero │ Acapulco │ 106 │
│ USA │ CA │ Pomona │ 106 │
│ USA │ WA │ Burien │ 106 │
│ Canada │ BC │ Langford │ 105 │
│ USA │ OR │ Lake Oswego │ 104 │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ USA │ CA │ Mill Valley │ 85 │
│ USA │ WA │ Spokane │ 85 │
│ USA │ OR │ Albany │ 84 │
│ USA │ CA │ Colma │ 84 │
│ USA │ CA │ Redwood City │ 84 │
│ USA │ CA │ Lakewood │ 82 │
│ Mexico │ Sinaloa │ La Cruz │ 78 │
│ USA │ CA │ Fremont │ 75 │
│ Mexico │ DF │ Santa Fe │ 51 │
│ USA │ CA │ San Francisco │ 47 │
├─────────┴──────────┴───────────────┴─────────────────────┤
│ 109 rows (20 shown) 4 columns │
└──────────────────────────────────────────────────────────┘
select country, state, city, count(*) as number_of_customers
from customer
group by country, state, city
order by number_of_customers desc;
7
- Revenue, Unitsales, Order Count per year, country and product family
- Order by tyear, store.country, product.family
┌───────┬─────────┬────────────────┬───────────────┬────────────────┬──────────────┐
│ TYear │ Country │ Family │ revenue │ sum(unitsales) │ count_star() │
│ int32 │ varchar │ varchar │ decimal(38,2) │ int128 │ int64 │
├───────┼─────────┼────────────────┼───────────────┼────────────────┼──────────────┤
│ 2014 │ USA │ Drink │ 48836.21 │ 24597 │ 7978 │
│ 2014 │ USA │ Food │ 409035.59 │ 191940 │ 62445 │
│ 2014 │ USA │ Non-Consumable │ 107366.33 │ 50236 │ 16414 │
│ 2015 │ Canada │ Drink │ 8669.84 │ 4354 │ 1380 │
│ 2015 │ Canada │ Food │ 70028.98 │ 32691 │ 10375 │
│ 2015 │ Canada │ Non-Consumable │ 19346.64 │ 9112 │ 2877 │
│ 2015 │ Mexico │ Drink │ 36890.31 │ 18483 │ 5921 │
│ 2015 │ Mexico │ Food │ 311041.43 │ 146642 │ 47052 │
│ 2015 │ Mexico │ Non-Consumable │ 82361.85 │ 38789 │ 12483 │
│ 2015 │ USA │ Drink │ 48182.01 │ 24117 │ 7811 │
│ 2015 │ USA │ Food │ 397065.39 │ 186590 │ 60716 │
│ 2015 │ USA │ Non-Consumable │ 105561.02 │ 49209 │ 15943 │
├───────┴─────────┴────────────────┴───────────────┴────────────────┴──────────────┤
│ 12 rows 6 columns │
└──────────────────────────────────────────────────────────────────────────────────┘
select tyear, store.country, product.family, sum(storesales) as revenue, sum(unitsales), count(*)
from sales
join timebyday on timebyday.timeid=sales.timeid
join store on sales.storeid=store.storeid
join product on product.productid=sales.productid
group by tyear, store.country, product.family
order by tyear, store.country, product.family;
Subquery
1
- All customers who have made purchases with store sales greater than the average store sales across all transactions
- Use distinct
- Order by Lastname, Firstname
┌────────────┬───────────┬────────────┐
│ Customerid │ Firstname │ Lastname │
│ int32 │ varchar │ varchar │
├────────────┼───────────┼────────────┤
│ 2724 │ Catherine │ Abahamdeh │
│ 3872 │ John │ Abalos │
│ 6979 │ Glen │ Abbassi │
│ 4881 │ Carroll │ Abbate │
│ 9163 │ Rusty │ Abbey │
│ 8288 │ Bonnie │ Abbott │
│ 278 │ Eric │ Abbott │
│ 8862 │ Patrick │ Abbott │
│ 6049 │ Sherry │ Abbott │
│ 1972 │ Muffy │ Abbruzzese │
│ · │ · │ · │
│ · │ · │ · │
│ · │ · │ · │
│ 4490 │ Roland │ Zumsteg │
│ 5613 │ Joyce │ Zuniga │
│ 8630 │ Shirley │ Zuniga │
│ 2049 │ Elizabeth │ Zuvich │
│ 5721 │ Harriett │ Zvibleman │
│ 6632 │ Ramona │ Zvibleman │
│ 1469 │ Todd │ Zweifel │
│ 3855 │ Julieanna │ Zwier │
│ 3801 │ Jeanne │ Zysko │
│ 5486 │ Mark │ Zywicki │
├────────────┴───────────┴────────────┤
│ 8075 rows (20 shown) 3 columns │
└─────────────────────────────────────┘
SELECT DISTINCT c.Customerid, c.Firstname, c.Lastname
FROM Customer c
JOIN Sales s ON c.Customerid = s.Customerid
WHERE s.Storesales > (SELECT AVG(Storesales) FROM Sales)
ORDER BY c.Lastname, c.Firstname;
2
- All products that have never been sold
- Order by Category, Pname
┌───────────┬─────────────────┬─────────┬──────────────────┐
│ Productid │ Pname │ Brand │ Category │
│ int32 │ varchar │ varchar │ varchar │
├───────────┼─────────────────┼─────────┼──────────────────┤
│ 1560 │ CDR Grape Jelly │ CDR │ Jams and Jellies │
└───────────┴─────────────────┴─────────┴──────────────────┘
SELECT p.Productid, p.Pname, p.Brand, p.Category
FROM Product p
WHERE p.Productid NOT IN (SELECT DISTINCT Productid FROM Sales)
ORDER BY p.Category, p.Pname;
3
- Top 3 countries by total store sales
- list all stores in those countries
- Order by Country, Sname
┌─────────┬──────────┬───────────────┬───────────┬─────────┐
│ Storeid │ Sname │ City │ State │ Country │
│ int32 │ varchar │ varchar │ varchar │ varchar │
├─────────┼──────────┼───────────────┼───────────┼─────────┤
│ 19 │ Store 19 │ Vancouver │ BC │ Canada │
│ 20 │ Store 20 │ Victoria │ BC │ Canada │
│ 1 │ Store 1 │ Acapulco │ Guerrero │ Mexico │
│ 10 │ Store 10 │ Orizaba │ Veracruz │ Mexico │
│ 12 │ Store 12 │ Hidalgo │ Zacatecas │ Mexico │
│ 18 │ Store 18 │ Hidalgo │ Zacatecas │ Mexico │
│ 21 │ Store 21 │ San Andres │ DF │ Mexico │
│ 4 │ Store 4 │ Camacho │ Zacatecas │ Mexico │
│ 5 │ Store 5 │ Guadalajara │ Jalisco │ Mexico │
│ 8 │ Store 8 │ Merida │ Yucatan │ Mexico │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ 15 │ Store 15 │ Seattle │ WA │ USA │
│ 16 │ Store 16 │ Spokane │ WA │ USA │
│ 17 │ Store 17 │ Tacoma │ WA │ USA │
│ 2 │ Store 2 │ Bellingham │ WA │ USA │
│ 22 │ Store 22 │ Walla Walla │ WA │ USA │
│ 23 │ Store 23 │ Yakima │ WA │ USA │
│ 24 │ Store 24 │ San Diego │ CA │ USA │
│ 3 │ Store 3 │ Bremerton │ WA │ USA │
│ 6 │ Store 6 │ Beverly Hills │ CA │ USA │
│ 7 │ Store 7 │ Los Angeles │ CA │ USA │
├─────────┴──────────┴───────────────┴───────────┴─────────┤
│ 25 rows (20 shown) 5 columns │
└──────────────────────────────────────────────────────────┘
WITH TopCountries AS (
SELECT st.Country, SUM(s.Storesales) as TotalSales
FROM Store st
JOIN Sales s ON st.Storeid = s.Storeid
GROUP BY st.Country
ORDER BY TotalSales DESC
LIMIT 3
)
SELECT s.Storeid, s.Sname, s.City, s.State, s.Country
FROM Store s
WHERE s.Country IN (SELECT Country FROM TopCountries)
ORDER BY s.Country, s.Sname;
4
- Total sales per product category
- Regard only categories where total sales exceeds 100000
- list all products in those categories
- Order by total sales descending, Pname
┌───────────┬────────────────────────────────┬────────────┬───────────────┐
│ Productid │ Pname │ Category │ TotalSales │
│ int32 │ varchar │ varchar │ decimal(38,2) │
├───────────┼────────────────────────────────┼────────────┼───────────────┤
│ 327 │ Better Canned Beets │ Vegetables │ 206070.44 │
│ 339 │ Better Canned Peas │ Vegetables │ 206070.44 │
│ 329 │ Better Canned String Beans │ Vegetables │ 206070.44 │
│ 333 │ Better Canned Tomatos │ Vegetables │ 206070.44 │
│ 331 │ Better Canned Yams │ Vegetables │ 206070.44 │
│ 328 │ Better Creamed Corn │ Vegetables │ 206070.44 │
│ 400 │ Big Time Fajita French Fries │ Vegetables │ 206070.44 │
│ 417 │ Big Time Frozen Broccoli │ Vegetables │ 206070.44 │
│ 406 │ Big Time Frozen Carrots │ Vegetables │ 206070.44 │
│ 416 │ Big Time Frozen Cauliflower │ Vegetables │ 206070.44 │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ 665 │ Gorilla Low Fat Cottage Cheese │ Dairy │ 108634.29 │
│ 666 │ Gorilla Low Fat Sour Cream │ Dairy │ 108634.29 │
│ 671 │ Gorilla Low Fat String Cheese │ Dairy │ 108634.29 │
│ 676 │ Gorilla Mild Cheddar Cheese │ Dairy │ 108634.29 │
│ 669 │ Gorilla Muenster Cheese │ Dairy │ 108634.29 │
│ 675 │ Gorilla Sharp Cheddar Cheese │ Dairy │ 108634.29 │
│ 667 │ Gorilla Sour Cream │ Dairy │ 108634.29 │
│ 683 │ Gorilla Strawberry Yogurt │ Dairy │ 108634.29 │
│ 670 │ Gorilla String Cheese │ Dairy │ 108634.29 │
│ 682 │ Gorilla Whole Milk │ Dairy │ 108634.29 │
├───────────┴────────────────────────────────┴────────────┴───────────────┤
│ 470 rows (20 shown) 4 columns │
└─────────────────────────────────────────────────────────────────────────┘
WITH CategorySales AS (
SELECT p.Category, SUM(s.Storesales) as TotalSales
FROM Product p
JOIN Sales s ON p.Productid = s.Productid
GROUP BY p.Category
)
SELECT p.Productid, p.Pname, p.Category, cs.TotalSales
FROM Product p
JOIN CategorySales cs ON p.Category = cs.Category
WHERE cs.TotalSales > 100000
ORDER BY cs.TotalSales DESC, p.Pname;
5
- Customers whose total yearly purchases are greater than the average total purchases of all customers in their state
- Order by State, TotalPurchases
┌────────────┬───────────┬───────────┬───────────┬────────────────┬───────────────────┐
│ Customerid │ Firstname │ Lastname │ State │ TotalPurchases │ AvgPurchases │
│ int32 │ varchar │ varchar │ varchar │ decimal(38,2) │ double │
├────────────┼───────────┼───────────┼───────────┼────────────────┼───────────────────┤
│ 5465 │ Dorothy │ Ahmari │ BC │ 342.21 │ 73.77386004514673 │
│ 1769 │ Joyce │ Minsky │ BC │ 291.13 │ 73.77386004514673 │
│ 6821 │ Katherine │ Worlay │ BC │ 282.91 │ 73.77386004514673 │
│ 59 │ Elizabeth │ Moss │ BC │ 277.51 │ 73.77386004514673 │
│ 943 │ David │ Chavez │ BC │ 267.34 │ 73.77386004514673 │
│ 5283 │ Jack │ Moore │ BC │ 254.28 │ 73.77386004514673 │
│ 2730 │ Helen │ Wright │ BC │ 250.25 │ 73.77386004514673 │
│ 8716 │ Gregory │ Wood │ BC │ 249.03 │ 73.77386004514673 │
│ 1777 │ Mary │ Hall │ BC │ 248.28 │ 73.77386004514673 │
│ 10281 │ Samuel │ Cartney │ BC │ 246.90 │ 73.77386004514673 │
│ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │
│ 2023 │ Kimberly │ Primack │ Zacatecas │ 870.25 │ 794.4078835978836 │
│ 4932 │ Zelda │ Stone │ Zacatecas │ 867.72 │ 794.4078835978836 │
│ 5712 │ Ralph │ Campbell │ Zacatecas │ 861.83 │ 794.4078835978836 │
│ 4571 │ David │ Bernahola │ Zacatecas │ 844.68 │ 794.4078835978836 │
│ 8522 │ Erelyn │ Stephens │ Zacatecas │ 837.50 │ 794.4078835978836 │
│ 3317 │ Aleah │ Elmore │ Zacatecas │ 834.04 │ 794.4078835978836 │
│ 2310 │ John │ Guzzetti │ Zacatecas │ 825.58 │ 794.4078835978836 │
│ 2510 │ Leslie │ Rainosek │ Zacatecas │ 824.96 │ 794.4078835978836 │
│ 8697 │ Gina │ Kerbel │ Zacatecas │ 802.97 │ 794.4078835978836 │
│ 2528 │ Ed │ Bias │ Zacatecas │ 795.47 │ 794.4078835978836 │
├────────────┴───────────┴───────────┴───────────┴────────────────┴───────────────────┤
│ 3633 rows (20 shown) 6 columns │
└─────────────────────────────────────────────────────────────────────────────────────┘
WITH CustomerTotals AS (
SELECT c.Customerid, c.State, SUM(s.Storesales) as TotalPurchases
FROM Customer c
JOIN Sales s ON c.Customerid = s.Customerid
GROUP BY c.Customerid, c.State
),
StateAverages AS (
SELECT State, AVG(TotalPurchases) as AvgPurchases
FROM CustomerTotals
GROUP BY State
)
SELECT c.Customerid, c.Firstname, c.Lastname, c.State, ct.TotalPurchases, sa.AvgPurchases
FROM Customer c
JOIN CustomerTotals ct ON c.Customerid = ct.Customerid
JOIN StateAverages sa ON c.State = sa.State
WHERE ct.TotalPurchases > sa.AvgPurchases
ORDER BY c.State, ct.TotalPurchases DESC;
Window Functions
1
- Rank products within each category by their total unit sales
- Show only top 5 products per category
- Order by Category, CategoryRank
┌───────────────────┬────────────────────────────────────────────┬────────────┬──────────────┐
│ Category │ Pname │ TotalUnits │ CategoryRank │
│ varchar │ varchar │ int128 │ int64 │
├───────────────────┼────────────────────────────────────────────┼────────────┼──────────────┤
│ Baking Goods │ BBB Best White Sugar │ 561 │ 1 │
│ Baking Goods │ BBB Best Sesame Oil │ 549 │ 2 │
│ Baking Goods │ BBB Best Pepper │ 547 │ 3 │
│ Baking Goods │ Super White Sugar │ 546 │ 4 │
│ Baking Goods │ Super Salt │ 542 │ 5 │
│ Bathroom Products │ Hilltop Mint Mouthwash │ 622 │ 1 │
│ Bathroom Products │ Hilltop Silky Smooth Hair Conditioner │ 611 │ 2 │
│ Bathroom Products │ Sunset Economy Toilet Brush │ 600 │ 3 │
│ Bathroom Products │ Consolidated Silky Smooth Hair Conditioner │ 562 │ 4 │
│ Bathroom Products │ Hilltop Extra Moisture Shampoo │ 554 │ 5 │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ Starchy Foods │ Monarch Rice Medly │ 600 │ 1 │
│ Starchy Foods │ Monarch Manicotti │ 593 │ 2 │
│ Starchy Foods │ Shady Lake Rice Medly │ 552 │ 3 │
│ Starchy Foods │ Colossal Manicotti │ 548 │ 4 │
│ Starchy Foods │ Monarch Spaghetti │ 547 │ 5 │
│ Vegetables │ Tell Tale Fresh Lima Beans │ 645 │ 1 │
│ Vegetables │ Hermanos Green Pepper │ 614 │ 2 │
│ Vegetables │ Hermanos Potatos │ 606 │ 3 │
│ Vegetables │ Hermanos Elephant Garlic │ 594 │ 4 │
│ Vegetables │ Ebony Fresh Lima Beans │ 593 │ 5 │
├───────────────────┴────────────────────────────────────────────┴────────────┴──────────────┤
│ 226 rows (20 shown) 4 columns │
└────────────────────────────────────────────────────────────────────────────────────────────┘
WITH ProductSales AS (
SELECT
p.Category,
p.Pname,
SUM(s.Unitsales) as TotalUnits,
RANK() OVER (PARTITION BY p.Category ORDER BY SUM(s.Unitsales) DESC) as CategoryRank
FROM Product p
JOIN Sales s ON p.Productid = s.Productid
GROUP BY p.Category, p.Pname
)
SELECT Category, Pname, TotalUnits, CategoryRank
FROM ProductSales
WHERE CategoryRank <= 5
ORDER BY Category, CategoryRank;
2
- Running total of store sales for each store ordered by month
- Order by Storeid, TYear, TMonthnumber
┌─────────┬──────────┬───────┬───────────┬───────────────┬───────────────┐
│ Storeid │ Sname │ TYear │ TMonth │ MonthlySales │ RunningTotal │
│ int32 │ varchar │ int32 │ varchar │ decimal(38,2) │ decimal(38,2) │
├─────────┼──────────┼───────┼───────────┼───────────────┼───────────────┤
│ 1 │ Store 1 │ 2015 │ January │ 5668.27 │ 5668.27 │
│ 1 │ Store 1 │ 2015 │ February │ 4212.25 │ 9880.52 │
│ 1 │ Store 1 │ 2015 │ March │ 5182.62 │ 15063.14 │
│ 1 │ Store 1 │ 2015 │ April │ 4604.30 │ 19667.44 │
│ 1 │ Store 1 │ 2015 │ May │ 4312.83 │ 23980.27 │
│ 1 │ Store 1 │ 2015 │ June │ 3384.40 │ 27364.67 │
│ 1 │ Store 1 │ 2015 │ July │ 4396.12 │ 31760.79 │
│ 1 │ Store 1 │ 2015 │ August │ 3686.99 │ 35447.78 │
│ 1 │ Store 1 │ 2015 │ September │ 4672.65 │ 40120.43 │
│ 1 │ Store 1 │ 2015 │ October │ 4301.57 │ 44422.00 │
│ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │
│ 24 │ Store 24 │ 2015 │ February │ 3940.47 │ 63101.32 │
│ 24 │ Store 24 │ 2015 │ March │ 5258.59 │ 68359.91 │
│ 24 │ Store 24 │ 2015 │ April │ 4169.44 │ 72529.35 │
│ 24 │ Store 24 │ 2015 │ May │ 4536.13 │ 77065.48 │
│ 24 │ Store 24 │ 2015 │ June │ 4866.19 │ 81931.67 │
│ 24 │ Store 24 │ 2015 │ July │ 4394.82 │ 86326.49 │
│ 24 │ Store 24 │ 2015 │ August │ 3938.25 │ 90264.74 │
│ 24 │ Store 24 │ 2015 │ September │ 4808.90 │ 95073.64 │
│ 24 │ Store 24 │ 2015 │ October │ 4252.11 │ 99325.75 │
│ 24 │ Store 24 │ 2015 │ November │ 6725.79 │ 106051.54 │
├─────────┴──────────┴───────┴───────────┴───────────────┴───────────────┤
│ 420 rows (20 shown) 6 columns │
└────────────────────────────────────────────────────────────────────────┘
SELECT
s.Storeid,
st.Sname,
t.TYear,
t.TMonth,
SUM(s.Storesales) as MonthlySales,
SUM(SUM(s.Storesales)) OVER (
PARTITION BY s.Storeid
ORDER BY t.TYear, t.TMonthnumber
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as RunningTotal
FROM Sales s
JOIN Store st ON s.Storeid = st.Storeid
JOIN TimeByDay t ON s.Timeid = t.Timeid
GROUP BY s.Storeid, st.Sname, t.TYear, t.TMonth, t.TMonthnumber
ORDER BY s.Storeid, t.TYear, t.TMonthnumber;
3
- Purchase amount per customer
- Show also next and previous purchase amounts
- Order by Customerid, TYear, TMonthnumber, TDayofmonth
┌────────────┬───────────┬──────────┬───────┬──────────┬─────────────┬───────────────┬──────────────────┬───────────────┐
│ Customerid │ Firstname │ Lastname │ TYear │ TMonth │ TDayofmonth │ Storesales │ PreviousPurchase │ NextPurchase │
│ int32 │ varchar │ varchar │ int32 │ varchar │ int32 │ decimal(13,2) │ decimal(13,2) │ decimal(13,2) │
├────────────┼───────────┼──────────┼───────┼──────────┼─────────────┼───────────────┼──────────────────┼───────────────┤
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 4.46 │ NULL │ 6.84 │
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 6.84 │ 4.46 │ 6.81 │
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 6.81 │ 6.84 │ 4.26 │
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 4.26 │ 6.81 │ 2.79 │
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 2.79 │ 4.26 │ 5.56 │
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 5.56 │ 2.79 │ 2.76 │
│ 3 │ Jeanne │ Derry │ 2014 │ April │ 27 │ 2.76 │ 5.56 │ 8.88 │
│ 3 │ Jeanne │ Derry │ 2014 │ October │ 22 │ 8.88 │ 2.76 │ 6.39 │
│ 3 │ Jeanne │ Derry │ 2014 │ October │ 22 │ 6.39 │ 8.88 │ 3.36 │
│ 3 │ Jeanne │ Derry │ 2014 │ October │ 22 │ 3.36 │ 6.39 │ 8.91 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 509 │ William │ Conner │ 2015 │ February │ 21 │ 3.04 │ NULL │ 7.68 │
│ 509 │ William │ Conner │ 2015 │ February │ 21 │ 7.68 │ 3.04 │ 4.92 │
│ 509 │ William │ Conner │ 2015 │ February │ 21 │ 4.92 │ 7.68 │ 13.76 │
│ 509 │ William │ Conner │ 2015 │ February │ 21 │ 13.76 │ 4.92 │ 5.73 │
│ 509 │ William │ Conner │ 2015 │ February │ 21 │ 5.73 │ 13.76 │ 5.26 │
│ 509 │ William │ Conner │ 2015 │ February │ 21 │ 5.26 │ 5.73 │ 8.96 │
│ 509 │ William │ Conner │ 2015 │ July │ 28 │ 8.96 │ 5.26 │ 8.22 │
│ 509 │ William │ Conner │ 2015 │ July │ 28 │ 8.22 │ 8.96 │ 10.68 │
│ 509 │ William │ Conner │ 2015 │ July │ 28 │ 10.68 │ 8.22 │ NULL │
│ 510 │ Ciro │ Bauer │ 2015 │ January │ 17 │ 6.84 │ NULL │ 2.82 │
├────────────┴───────────┴──────────┴───────┴──────────┴─────────────┴───────────────┴──────────────────┴───────────────┤
│ ? rows (>9999 rows, 20 shown) 9 columns │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
SELECT
c.Customerid,
c.Firstname,
c.Lastname,
t.TYear,
t.TMonth,
t.TDayofmonth,
s.Storesales,
LAG(s.Storesales) OVER (PARTITION BY c.Customerid ORDER BY t.TYear, t.TMonthnumber, t.TDayofmonth) as PreviousPurchase,
LEAD(s.Storesales) OVER (PARTITION BY c.Customerid ORDER BY t.TYear, t.TMonthnumber, t.TDayofmonth) as NextPurchase
FROM Customer c
JOIN Sales s ON c.Customerid = s.Customerid
JOIN TimeByDay t ON s.Timeid = t.Timeid
ORDER BY c.Customerid, t.TYear, t.TMonthnumber, t.TDayofmonth;
4
- Percentage contribution of each product's sales to its department's total sale
- Order by Department, ProductSales descending
┌─────────────────────┬────────────────────────────┬───────────────┬─────────────────┬────────────────────────┐
│ Department │ Pname │ ProductSales │ DepartmentSales │ PercentageContribution │
│ varchar │ varchar │ decimal(38,2) │ decimal(38,2) │ double │
├─────────────────────┼────────────────────────────┼───────────────┼─────────────────┼────────────────────────┤
│ Alcoholic Beverages │ Portsmouth Light Beer │ 1989.75 │ 41137.07 │ 4.84 │
│ Alcoholic Beverages │ Good White Zinfandel Wine │ 1951.09 │ 41137.07 │ 4.74 │
│ Alcoholic Beverages │ Portsmouth Chardonnay Wine │ 1824.76 │ 41137.07 │ 4.44 │
│ Alcoholic Beverages │ Top Measure Light Wine │ 1710.24 │ 41137.07 │ 4.16 │
│ Alcoholic Beverages │ Walrus Chardonnay │ 1702.40 │ 41137.07 │ 4.14 │
│ Alcoholic Beverages │ Top Measure Chablis Wine │ 1612.16 │ 41137.07 │ 3.92 │
│ Alcoholic Beverages │ Good Chablis Wine │ 1550.64 │ 41137.07 │ 3.77 │
│ Alcoholic Beverages │ Pearl Chablis Wine │ 1511.52 │ 41137.07 │ 3.67 │
│ Alcoholic Beverages │ Portsmouth Merlot Wine │ 1469.44 │ 41137.07 │ 3.57 │
│ Alcoholic Beverages │ Walrus Merlot Wine │ 1468.28 │ 41137.07 │ 3.57 │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ Starchy Foods │ Discover Manicotti │ 827.54 │ 33891.81 │ 2.44 │
│ Starchy Foods │ Monarch Spaghetti │ 754.86 │ 33891.81 │ 2.23 │
│ Starchy Foods │ Medalist Manicotti │ 692.78 │ 33891.81 │ 2.04 │
│ Starchy Foods │ Colossal Rice Medly │ 692.04 │ 33891.81 │ 2.04 │
│ Starchy Foods │ Jardon Manicotti │ 596.40 │ 33891.81 │ 1.76 │
│ Starchy Foods │ Jardon Ravioli │ 503.04 │ 33891.81 │ 1.48 │
│ Starchy Foods │ Discover Ravioli │ 402.16 │ 33891.81 │ 1.19 │
│ Starchy Foods │ Shady Lake Rice Medly │ 386.40 │ 33891.81 │ 1.14 │
│ Starchy Foods │ Jardon Rice Medly │ 259.26 │ 33891.81 │ 0.76 │
│ Starchy Foods │ Jardon Spaghetti │ 235.44 │ 33891.81 │ 0.69 │
├─────────────────────┴────────────────────────────┴───────────────┴─────────────────┴────────────────────────┤
│ 1559 rows (20 shown) 5 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
SELECT
p.Department,
p.Pname,
SUM(s.Storesales) as ProductSales,
SUM(SUM(s.Storesales)) OVER (PARTITION BY p.Department) as DepartmentSales,
ROUND(100.0 * SUM(s.Storesales) / SUM(SUM(s.Storesales)) OVER (PARTITION BY p.Department), 2) as PercentageContribution
FROM Product p
JOIN Sales s ON p.Productid = s.Productid
GROUP BY p.Department, p.Pname
ORDER BY p.Department, ProductSales DESC;
5
- Assign quartiles to customers based on their total yearly income
- within each education level
- grouping customers into four equal groups
- Order by Education, SpendingQuartile, TotalSpent
┌────────────┬───────────┬────────────┬─────────────────────┬───────────────┬───────────────┬──────────────────┐
│ Customerid │ Firstname │ Lastname │ Education │ Yearlyincome │ TotalSpent │ SpendingQuartile │
│ int32 │ varchar │ varchar │ varchar │ varchar │ decimal(38,2) │ int64 │
├────────────┼───────────┼────────────┼─────────────────────┼───────────────┼───────────────┼──────────────────┤
│ 3425 │ Joseph │ Fernandez │ Bachelors Degree │ $150K + │ 55.31 │ 1 │
│ 9786 │ Bruce │ Berg │ Bachelors Degree │ $50K - $70K │ 55.11 │ 1 │
│ 6038 │ Erica │ Wall │ Bachelors Degree │ $30K - $50K │ 55.07 │ 1 │
│ 1950 │ Kathaleen │ Cohen │ Bachelors Degree │ $50K - $70K │ 55.05 │ 1 │
│ 7555 │ Debbie │ Smiglewski │ Bachelors Degree │ $70K - $90K │ 55.01 │ 1 │
│ 8371 │ Harold │ Cohen │ Bachelors Degree │ $50K - $70K │ 54.98 │ 1 │
│ 2685 │ Myrna │ Brauer │ Bachelors Degree │ $50K - $70K │ 54.95 │ 1 │
│ 9859 │ Genevieve │ Russo │ Bachelors Degree │ $70K - $90K │ 54.92 │ 1 │
│ 5012 │ Wayne │ Thomas │ Bachelors Degree │ $50K - $70K │ 54.80 │ 1 │
│ 8101 │ Robert │ Nelson │ Bachelors Degree │ $130K - $150K │ 54.72 │ 1 │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ 9574 │ Willie │ Zamora │ Partial High School │ $10K - $30K │ 224.13 │ 4 │
│ 6570 │ Christine │ Avila │ Partial High School │ $10K - $30K │ 224.03 │ 4 │
│ 7731 │ Helen │ Villa │ Partial High School │ $10K - $30K │ 223.61 │ 4 │
│ 8247 │ Elizabeth │ Carter │ Partial High School │ $50K - $70K │ 222.66 │ 4 │
│ 8730 │ Richard │ Parsley │ Partial High School │ $10K - $30K │ 222.21 │ 4 │
│ 7383 │ Steve │ Vizcarra │ Partial High School │ $10K - $30K │ 221.85 │ 4 │
│ 1511 │ Kimberly │ Ketchum │ Partial High School │ $10K - $30K │ 221.70 │ 4 │
│ 2736 │ Kathleen │ Sgambati │ Partial High School │ $10K - $30K │ 221.54 │ 4 │
│ 3277 │ Diane │ Waterhouse │ Partial High School │ $10K - $30K │ 221.46 │ 4 │
│ 10222 │ Kathleen │ Cantu │ Partial High School │ $10K - $30K │ 221.45 │ 4 │
├────────────┴───────────┴────────────┴─────────────────────┴───────────────┴───────────────┴──────────────────┤
│ 8736 rows (20 shown) 7 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
WITH CustomerSales AS (
SELECT
c.Customerid,
c.Firstname,
c.Lastname,
c.Education,
c.Yearlyincome,
SUM(s.Storesales) as TotalSpent
FROM Customer c
JOIN Sales s ON c.Customerid = s.Customerid
GROUP BY c.Customerid, c.Firstname, c.Lastname, c.Education, c.Yearlyincome
)
SELECT
Customerid,
Firstname,
Lastname,
Education,
Yearlyincome,
TotalSpent,
NTILE(4) OVER (PARTITION BY Education ORDER BY TotalSpent) as SpendingQuartile
FROM CustomerSales
ORDER BY Education, SpendingQuartile, TotalSpent DESC;