Query Examples

These query examples are taken from the site PostgreSQL Exercises. A sample data-set can be found on the getting started page.

Here is a visual representation of the schema used in these examples:

../_images/schema-horizontal.png

Model Definitions

To begin working with the data, we’ll define the model classes that correspond to the tables in the diagram.

Note

In some cases we explicitly specify column names for a particular field. This is so our models are compatible with the database schema used for the postgres exercises.

  1. from functools import partial
  2. from peewee import *
  5. db = PostgresqlDatabase('peewee_test')
  7. class BaseModel(Model):
  8.     class Meta:
  9.         database = db
  11. class Member(BaseModel):
  12.     memid = AutoField()  # Auto-incrementing primary key.
  13.     surname = CharField()
  14.     firstname = CharField()
  15.     address = CharField(max_length=300)
  16.     zipcode = IntegerField()
  17.     telephone = CharField()
  18.     recommendedby = ForeignKeyField('self', backref='recommended',
  19.                                     column_name='recommendedby', null=True)
  20.     joindate = DateTimeField()
  22.     class Meta:
  23.         table_name = 'members'
  26. # Conveniently declare decimal fields suitable for storing currency.
  27. MoneyField = partial(DecimalField, decimal_places=2)
  30. class Facility(BaseModel):
  31.     facid = AutoField()
  32.     name = CharField()
  33.     membercost = MoneyField()
  34.     guestcost = MoneyField()
  35.     initialoutlay = MoneyField()
  36.     monthlymaintenance = MoneyField()
  38.     class Meta:
  39.         table_name = 'facilities'
  42. class Booking(BaseModel):
  43.     bookid = AutoField()
  44.     facility = ForeignKeyField(Facility, column_name='facid')
  45.     member = ForeignKeyField(Member, column_name='memid')
  46.     starttime = DateTimeField()
  47.     slots = IntegerField()
  49.     class Meta:
  50.         table_name = 'bookings'

Schema Creation

If you downloaded the SQL file from the PostgreSQL Exercises site, then you can load the data into a PostgreSQL database using the following commands:

  1. createdb peewee_test
  2. psql -U postgres -f clubdata.sql -d peewee_test -x -q

To create the schema using Peewee, without loading the sample data, you can run the following:

  1. # Assumes you have created the database "peewee_test" already.
  2. db.create_tables([Member, Facility, Booking])

Basic Exercises

This category deals with the basics of SQL. It covers select and where clauses, case expressions, unions, and a few other odds and ends.

Retrieve everything

Retrieve all information from facilities table.

  1. SELECT * FROM facilities
  1. # By default, when no fields are explicitly passed to select(), all fields
  2. # will be selected.
  3. query = Facility.select()

Retrieve specific columns from a table

Retrieve names of facilities and cost to members.

  1. SELECT name, membercost FROM facilities;
  1. query = Facility.select(Facility.name, Facility.membercost)
  3. # To iterate:
  4. for facility in query:
  5.     print(facility.name)

Control which rows are retrieved

Retrieve list of facilities that have a cost to members.

  1. SELECT * FROM facilities WHERE membercost > 0
  1. query = Facility.select().where(Facility.membercost > 0)

Control which rows are retrieved - part 2

Retrieve list of facilities that have a cost to members, and that fee is less than 1/50th of the monthly maintenance cost. Return id, name, cost and monthly-maintenance.

  1. SELECT facid, name, membercost, monthlymaintenance
  2. FROM facilities
  3. WHERE membercost > 0 AND membercost < (monthlymaintenance / 50)
  1. query = (Facility
  2.          .select(Facility.facid, Facility.name, Facility.membercost,
  3.                  Facility.monthlymaintenance)
  4.          .where(
  5.              (Facility.membercost > 0) &
  6.              (Facility.membercost < (Facility.monthlymaintenance / 50))))

Basic string searches

How can you produce a list of all facilities with the word ‘Tennis’ in their name?

  1. SELECT * FROM facilities WHERE name ILIKE '%tennis%';
  1. query = Facility.select().where(Facility.name.contains('tennis'))
  3. # OR use the exponent operator. Note: you must include wildcards here:
  4. query = Facility.select().where(Facility.name ** '%tennis%')

Matching against multiple possible values

How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.

  1. SELECT * FROM facilities WHERE facid IN (1, 5);
  1. query = Facility.select().where(Facility.facid.in_([1, 5]))
  3. # OR:
  4. query = Facility.select().where((Facility.facid == 1) |
  5.                                 (Facility.facid == 5))

Classify results into buckets

How can you produce a list of facilities, with each labelled as ‘cheap’ or ‘expensive’ depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.

  1. SELECT name,
  2. CASE WHEN monthlymaintenance > 100 THEN 'expensive' ELSE 'cheap' END
  3. FROM facilities;
  1. cost = Case(None, [(Facility.monthlymaintenance > 100, 'expensive')], 'cheap')
  2. query = Facility.select(Facility.name, cost.alias('cost'))

Note

See documentation Case for more examples.

Working with dates

How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.

  1. SELECT memid, surname, firstname, joindate FROM members
  2. WHERE joindate >= '2012-09-01';
  1. query = (Member
  2.          .select(Member.memid, Member.surname, Member.firstname, Member.joindate)
  3.          .where(Member.joindate >= datetime.date(2012, 9, 1)))

Removing duplicates, and ordering results

How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

  1. SELECT DISTINCT surname FROM members ORDER BY surname LIMIT 10;
  1. query = (Member
  2.          .select(Member.surname)
  3.          .order_by(Member.surname)
  4.          .limit(10)
  5.          .distinct())

Combining results from multiple queries

You, for some reason, want a combined list of all surnames and all facility names.

  1. SELECT surname FROM members UNION SELECT name FROM facilities;
  1. lhs = Member.select(Member.surname)
  2. rhs = Facility.select(Facility.name)
  3. query = lhs | rhs

Queries can be composed using the following operators:

  • | - UNION
  • + - UNION ALL
  • & - INTERSECT
  • - - EXCEPT

Simple aggregation

You’d like to get the signup date of your last member. How can you retrieve this information?

  1. SELECT MAX(join_date) FROM members;
  1. query = Member.select(fn.MAX(Member.joindate))
  2. # To conveniently obtain a single scalar value, use "scalar()":
  3. # max_join_date = query.scalar()

More aggregation

You’d like to get the first and last name of the last member(s) who signed up - not just the date.

  1. SELECT firstname, surname, joindate FROM members
  2. WHERE joindate = (SELECT MAX(joindate) FROM members);
  1. # Use "alias()" to reference the same table multiple times in a query.
  2. MemberAlias = Member.alias()
  3. subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
  4. query = (Member
  5.          .select(Member.firstname, Member.surname, Member.joindate)
  6.          .where(Member.joindate == subq))

Joins and Subqueries

This category deals primarily with a foundational concept in relational database systems: joining. Joining allows you to combine related information from multiple tables to answer a question. This isn’t just beneficial for ease of querying: a lack of join capability encourages denormalisation of data, which increases the complexity of keeping your data internally consistent.

This topic covers inner, outer, and self joins, as well as spending a little time on subqueries (queries within queries).

Retrieve the start times of members’ bookings

How can you produce a list of the start times for bookings by members named ‘David Farrell’?

  1. SELECT starttime FROM bookings
  2. INNER JOIN members ON (bookings.memid = members.memid)
  3. WHERE surname = 'Farrell' AND firstname = 'David';
  1. query = (Booking
  2.          .select(Booking.starttime)
  3.          .join(Member)
  4.          .where((Member.surname == 'Farrell') &
  5.                 (Member.firstname == 'David')))

Work out the start times of bookings for tennis courts

How can you produce a list of the start times for bookings for tennis courts, for the date ‘2012-09-21’? Return a list of start time and facility name pairings, ordered by the time.

  1. SELECT starttime, name
  2. FROM bookings
  3. INNER JOIN facilities ON (bookings.facid = facilities.facid)
  4. WHERE date_trunc('day', starttime) = '2012-09-21':: date
  5.   AND name ILIKE 'tennis%'
  6. ORDER BY starttime, name;
  1. query = (Booking
  2.          .select(Booking.starttime, Facility.name)
  3.          .join(Facility)
  4.          .where(
  5.              (fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 21)) &
  6.              Facility.name.startswith('Tennis'))
  7.          .order_by(Booking.starttime, Facility.name))
  9. # To retrieve the joined facility's name when iterating:
  10. for booking in query:
  11.     print(booking.starttime, booking.facility.name)

How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).

  1. SELECT DISTINCT m.firstname, m.surname
  2. FROM members AS m2
  3. INNER JOIN members AS m ON (m.memid = m2.recommendedby)
  4. ORDER BY m.surname, m.firstname;
  1. MA = Member.alias()
  2. query = (Member
  3.          .select(Member.firstname, Member.surname)
  4.          .join(MA, on=(MA.recommendedby == Member.memid))
  5.          .order_by(Member.surname, Member.firstname))

Produce a list of all members, along with their recommender

How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).

  1. SELECT m.firstname, m.surname, r.firstname, r.surname
  2. FROM members AS m
  3. LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid)
  4. ORDER BY m.surname, m.firstname
  1. MA = Member.alias()
  2. query = (Member
  3.          .select(Member.firstname, Member.surname, MA.firstname, MA.surname)
  4.          .join(MA, JOIN.LEFT_OUTER, on=(Member.recommendedby == MA.memid))
  5.          .order_by(Member.surname, Member.firstname))
  7. # To display the recommender's name when iterating:
  8. for m in query:
  9.     print(m.firstname, m.surname)
  10.     if m.recommendedby:
  11.         print('  ', m.recommendedby.firstname, m.recommendedby.surname)

Produce a list of all members who have used a tennis court

How can you produce a list of all members who have used a tennis court? Include in your output the name of the court, and the name of the member formatted as a single column. Ensure no duplicate data, and order by the member name.

  1. SELECT DISTINCT m.firstname || ' ' || m.surname AS member, f.name AS facility
  2. FROM members AS m
  3. INNER JOIN bookings AS b ON (m.memid = b.memid)
  4. INNER JOIN facilities AS f ON (b.facid = f.facid)
  5. WHERE f.name LIKE 'Tennis%'
  6. ORDER BY member, facility;
  1. fullname = Member.firstname + ' ' + Member.surname
  2. query = (Member
  3.          .select(fullname.alias('member'), Facility.name.alias('facility'))
  4.          .join(Booking)
  5.          .join(Facility)
  6.          .where(Facility.name.startswith('Tennis'))
  7.          .order_by(fullname, Facility.name)
  8.          .distinct())

Produce a list of costly bookings

How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour ‘slot’), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost, and do not use any subqueries.

  1. SELECT m.firstname || ' ' || m.surname AS member,
  2.        f.name AS facility,
  3.        (CASE WHEN m.memid = 0 THEN f.guestcost * b.slots
  4.         ELSE f.membercost * b.slots END) AS cost
  5. FROM members AS m
  6. INNER JOIN bookings AS b ON (m.memid = b.memid)
  7. INNER JOIN facilities AS f ON (b.facid = f.facid)
  8. WHERE (date_trunc('day', b.starttime) = '2012-09-14') AND
  9.  ((m.memid = 0 AND b.slots * f.guestcost > 30) OR
  10.   (m.memid > 0 AND b.slots * f.membercost > 30))
  11. ORDER BY cost DESC;
  1. cost = Case(Member.memid, (
  2.     (0, Booking.slots * Facility.guestcost),
  3. ), (Booking.slots * Facility.membercost))
  4. fullname = Member.firstname + ' ' + Member.surname
  6. query = (Member
  7.          .select(fullname.alias('member'), Facility.name.alias('facility'),
  8.                  cost.alias('cost'))
  9.          .join(Booking)
  10.          .join(Facility)
  11.          .where(
  12.              (fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)) &
  13.              (cost > 30))
  14.          .order_by(SQL('cost').desc()))
  16. # To iterate over the results, it might be easiest to use namedtuples:
  17. for row in query.namedtuples():
  18.     print(row.member, row.facility, row.cost)

Produce a list of all members, along with their recommender, using no joins.

How can you output a list of all members, including the individual who recommended them (if any), without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.

  1. SELECT DISTINCT m.firstname || ' ' || m.surname AS member,
  2.    (SELECT r.firstname || ' ' || r.surname
  3.     FROM cd.members AS r
  4.     WHERE m.recommendedby = r.memid) AS recommended
  5. FROM members AS m ORDER BY member;
  1. MA = Member.alias()
  2. subq = (MA
  3.         .select(MA.firstname + ' ' + MA.surname)
  4.         .where(Member.recommendedby == MA.memid))
  5. query = (Member
  6.          .select(fullname.alias('member'), subq.alias('recommended'))
  7.          .order_by(fullname))

Produce a list of costly bookings, using a subquery

The “Produce a list of costly bookings” exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE statement. Try to simplify this calculation using subqueries.

  1. SELECT member, facility, cost from (
  2.   SELECT
  3.   m.firstname || ' ' || m.surname as member,
  4.   f.name as facility,
  5.   CASE WHEN m.memid = 0 THEN b.slots * f.guestcost
  6.   ELSE b.slots * f.membercost END AS cost
  7.   FROM members AS m
  8.   INNER JOIN bookings AS b ON m.memid = b.memid
  9.   INNER JOIN facilities AS f ON b.facid = f.facid
  10.   WHERE date_trunc('day', b.starttime) = '2012-09-14'
  11. ) as bookings
  12. WHERE cost > 30
  13. ORDER BY cost DESC;
  1. cost = Case(Member.memid, (
  2.     (0, Booking.slots * Facility.guestcost),
  3. ), (Booking.slots * Facility.membercost))
  5. iq = (Member
  6.       .select(fullname.alias('member'), Facility.name.alias('facility'),
  7.               cost.alias('cost'))
  8.       .join(Booking)
  9.       .join(Facility)
  10.       .where(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)))
  12. query = (Member
  13.          .select(iq.c.member, iq.c.facility, iq.c.cost)
  14.          .from_(iq)
  15.          .where(iq.c.cost > 30)
  16.          .order_by(SQL('cost').desc()))
  18. # To iterate, try using dicts:
  19. for row in query.dicts():
  20.     print(row['member'], row['facility'], row['cost'])

Modifying Data

Querying data is all well and good, but at some point you’re probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. Operations that alter your data like this are collectively known as Data Manipulation Language, or DML.

In previous sections, we returned to you the results of the query you’ve performed. Since modifications like the ones we’re making in this section don’t return any query results, we instead show you the updated content of the table you’re supposed to be working on.

Insert some data into a table

The club is adding a new facility - a spa. We need to add it into the facilities table. Use the following values: facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800

  1. INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
  2. "initialoutlay", "monthlymaintenance") VALUES (9, 'Spa', 20, 30, 100000, 800)
  1. res = Facility.insert({
  2.     Facility.facid: 9,
  3.     Facility.name: 'Spa',
  4.     Facility.membercost: 20,
  5.     Facility.guestcost: 30,
  6.     Facility.initialoutlay: 100000,
  7.     Facility.monthlymaintenance: 800}).execute()
  9. # OR:
  10. res = (Facility
  11.        .insert(facid=9, name='Spa', membercost=20, guestcost=30,
  12.                initialoutlay=100000, monthlymaintenance=800)
  13.        .execute())

Insert multiple rows of data into a table

In the previous exercise, you learned how to add a facility. Now you’re going to add multiple facilities in one command. Use the following values:

facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

facid: 10, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.

  1. -- see above --
  1. data = [
  2.     {'facid': 9, 'name': 'Spa', 'membercost': 20, 'guestcost': 30,
  3.      'initialoutlay': 100000, 'monthlymaintenance': 800},
  4.     {'facid': 10, 'name': 'Squash Court 2', 'membercost': 3.5,
  5.      'guestcost': 17.5, 'initialoutlay': 5000, 'monthlymaintenance': 80}]
  6. res = Facility.insert_many(data).execute()

Insert calculated data into a table

Let’s try adding the spa to the facilities table again. This time, though, we want to automatically generate the value for the next facid, rather than specifying it as a constant. Use the following values for everything else: Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

  1. INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
  2.   "initialoutlay", "monthlymaintenance")
  3. SELECT (SELECT (MAX("facid") + 1) FROM "facilities") AS _,
  4.         'Spa', 20, 30, 100000, 800;
  1. maxq = Facility.select(fn.MAX(Facility.facid) + 1)
  2. subq = Select(columns=(maxq, 'Spa', 20, 30, 100000, 800))
  3. res = Facility.insert_from(subq, Facility._meta.sorted_fields).execute()

Update some existing data

We made a mistake when entering the data for the second tennis court. The initial outlay was 10000 rather than 8000: you need to alter the data to fix the error.

  1. UPDATE facilities SET initialoutlay = 10000 WHERE name = 'Tennis Court 2';
  1. res = (Facility
  2.        .update({Facility.initialoutlay: 10000})
  3.        .where(Facility.name == 'Tennis Court 2')
  4.        .execute())
  6. # OR:
  7. res = (Facility
  8.        .update(initialoutlay=10000)
  9.        .where(Facility.name == 'Tennis Court 2')
  10.        .execute())

Update multiple rows and columns at the same time

We want to increase the price of the tennis courts for both members and guests. Update the costs to be 6 for members, and 30 for guests.

  1. UPDATE facilities SET membercost=6, guestcost=30 WHERE name ILIKE 'Tennis%';
  1. nrows = (Facility
  2.          .update(membercost=6, guestcost=30)
  3.          .where(Facility.name.startswith('Tennis'))
  4.          .execute())

Update a row based on the contents of another row

We want to alter the price of the second tennis court so that it costs 10% more than the first one. Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.

  1. UPDATE facilities SET
  2. membercost = (SELECT membercost * 1.1 FROM facilities WHERE facid = 0),
  3. guestcost = (SELECT guestcost * 1.1 FROM facilities WHERE facid = 0)
  4. WHERE facid = 1;
  6. -- OR --
  7. WITH new_prices (nmc, ngc) AS (
  8.   SELECT membercost * 1.1, guestcost * 1.1
  9.   FROM facilities WHERE name = 'Tennis Court 1')
  10. UPDATE facilities
  11. SET membercost = new_prices.nmc, guestcost = new_prices.ngc
  12. FROM new_prices
  13. WHERE name = 'Tennis Court 2'
  1. sq1 = Facility.select(Facility.membercost * 1.1).where(Facility.facid == 0)
  2. sq2 = Facility.select(Facility.guestcost * 1.1).where(Facility.facid == 0)
  4. res = (Facility
  5.        .update(membercost=sq1, guestcost=sq2)
  6.        .where(Facility.facid == 1)
  7.        .execute())
  9. # OR:
  10. cte = (Facility
  11.        .select(Facility.membercost * 1.1, Facility.guestcost * 1.1)
  12.        .where(Facility.name == 'Tennis Court 1')
  13.        .cte('new_prices', columns=('nmc', 'ngc')))
  14. res = (Facility
  15.        .update(membercost=SQL('new_prices.nmc'), guestcost=SQL('new_prices.ngc'))
  16.        .with_cte(cte)
  17.        .from_(cte)
  18.        .where(Facility.name == 'Tennis Court 2')
  19.        .execute())

Delete all bookings

As part of a clearout of our database, we want to delete all bookings from the bookings table.

  1. DELETE FROM bookings;
  1. nrows = Booking.delete().execute()

Delete a member from the cd.members table

We want to remove member 37, who has never made a booking, from our database.

  1. DELETE FROM members WHERE memid = 37;
  1. nrows = Member.delete().where(Member.memid == 37).execute()

Delete based on a subquery

How can we make that more general, to delete all members who have never made a booking?

  1. DELETE FROM members WHERE NOT EXISTS (
  2.   SELECT * FROM bookings WHERE bookings.memid = members.memid);
  1. subq = Booking.select().where(Booking.member == Member.memid)
  2. nrows = Member.delete().where(~fn.EXISTS(subq)).execute()

Aggregation

Aggregation is one of those capabilities that really make you appreciate the power of relational database systems. It allows you to move beyond merely persisting your data, into the realm of asking truly interesting questions that can be used to inform decision making. This category covers aggregation at length, making use of standard grouping as well as more recent window functions.

Count the number of facilities

For our first foray into aggregates, we’re going to stick to something simple. We want to know how many facilities exist - simply produce a total count.

  1. SELECT COUNT(facid) FROM facilities;
  1. query = Facility.select(fn.COUNT(Facility.facid))
  2. count = query.scalar()
  4. # OR:
  5. count = Facility.select().count()

Count the number of expensive facilities

Produce a count of the number of facilities that have a cost to guests of 10 or more.

  1. SELECT COUNT(facid) FROM facilities WHERE guestcost >= 10
  1. query = Facility.select(fn.COUNT(Facility.facid)).where(Facility.guestcost >= 10)
  2. count = query.scalar()
  4. # OR:
  5. # count = Facility.select().where(Facility.guestcost >= 10).count()

Count the number of recommendations each member makes.

Produce a count of the number of recommendations each member has made. Order by member ID.

  1. SELECT recommendedby, COUNT(memid) FROM members
  2. WHERE recommendedby IS NOT NULL
  3. GROUP BY recommendedby
  4. ORDER BY recommendedby
  1. query = (Member
  2.          .select(Member.recommendedby, fn.COUNT(Member.memid))
  3.          .where(Member.recommendedby.is_null(False))
  4.          .group_by(Member.recommendedby)
  5.          .order_by(Member.recommendedby))

List the total slots booked per facility

Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id.

  1. SELECT facid, SUM(slots) FROM bookings GROUP BY facid ORDER BY facid;
  1. query = (Booking
  2.          .select(Booking.facid, fn.SUM(Booking.slots))
  3.          .group_by(Booking.facid)
  4.          .order_by(Booking.facid))

List the total slots booked per facility in a given month

Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.

  1. SELECT facid, SUM(slots)
  2. FROM bookings
  3. WHERE (date_trunc('month', starttime) = '2012-09-01'::dates)
  4. GROUP BY facid
  5. ORDER BY SUM(slots)
  1. query = (Booking
  2.          .select(Booking.facility, fn.SUM(Booking.slots))
  3.          .where(fn.date_trunc('month', Booking.starttime) == datetime.date(2012, 9, 1))
  4.          .group_by(Booking.facility)
  5.          .order_by(fn.SUM(Booking.slots)))

List the total slots booked per facility per month

Produce a list of the total number of slots booked per facility per month in the year of 2012. Produce an output table consisting of facility id and slots, sorted by the id and month.

  1. SELECT facid, date_part('month', starttime), SUM(slots)
  2. FROM bookings
  3. WHERE date_part('year', starttime) = 2012
  4. GROUP BY facid, date_part('month', starttime)
  5. ORDER BY facid, date_part('month', starttime)
  1. month = fn.date_part('month', Booking.starttime)
  2. query = (Booking
  3.          .select(Booking.facility, month, fn.SUM(Booking.slots))
  4.          .where(fn.date_part('year', Booking.starttime) == 2012)
  5.          .group_by(Booking.facility, month)
  6.          .order_by(Booking.facility, month))

Find the count of members who have made at least one booking

Find the total number of members who have made at least one booking.

  1. SELECT COUNT(DISTINCT memid) FROM bookings
  3. -- OR --
  4. SELECT COUNT(1) FROM (SELECT DISTINCT memid FROM bookings) AS _
  1. query = Booking.select(fn.COUNT(Booking.member.distinct()))
  3. # OR:
  4. query = Booking.select(Booking.member).distinct()
  5. count = query.count()  # count() wraps in SELECT COUNT(1) FROM (...)

List facilities with more than 1000 slots booked

Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and hours, sorted by facility id.

  1. SELECT facid, SUM(slots) FROM bookings
  2. GROUP BY facid
  3. HAVING SUM(slots) > 1000
  4. ORDER BY facid;
  1. query = (Booking
  2.          .select(Booking.facility, fn.SUM(Booking.slots))
  3.          .group_by(Booking.facility)
  4.          .having(fn.SUM(Booking.slots) > 1000)
  5.          .order_by(Booking.facility))

Find the total revenue of each facility

Produce a list of facilities along with their total revenue. The output table should consist of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!

  1. SELECT f.name, SUM(b.slots * (
  2. CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
  3. FROM bookings AS b
  4. INNER JOIN facilities AS f ON b.facid = f.facid
  5. GROUP BY f.name
  6. ORDER BY revenue;
  1. revenue = fn.SUM(Booking.slots * Case(None, (
  2.     (Booking.member == 0, Facility.guestcost),
  3. ), Facility.membercost))
  5. query = (Facility
  6.          .select(Facility.name, revenue.alias('revenue'))
  7.          .join(Booking)
  8.          .group_by(Facility.name)
  9.          .order_by(SQL('revenue')))

Find facilities with a total revenue less than 1000

Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!

  1. SELECT f.name, SUM(b.slots * (
  2. CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
  3. FROM bookings AS b
  4. INNER JOIN facilities AS f ON b.facid = f.facid
  5. GROUP BY f.name
  6. HAVING SUM(b.slots * ...) < 1000
  7. ORDER BY revenue;
  1. # Same definition as previous example.
  2. revenue = fn.SUM(Booking.slots * Case(None, (
  3.     (Booking.member == 0, Facility.guestcost),
  4. ), Facility.membercost))
  6. query = (Facility
  7.          .select(Facility.name, revenue.alias('revenue'))
  8.          .join(Booking)
  9.          .group_by(Facility.name)
  10.          .having(revenue < 1000)
  11.          .order_by(SQL('revenue')))

Output the facility id that has the highest number of slots booked

Output the facility id that has the highest number of slots booked.

  1. SELECT facid, SUM(slots) FROM bookings
  2. GROUP BY facid
  3. ORDER BY SUM(slots) DESC
  4. LIMIT 1
  1. query = (Booking
  2.          .select(Booking.facility, fn.SUM(Booking.slots))
  3.          .group_by(Booking.facility)
  4.          .order_by(fn.SUM(Booking.slots).desc())
  5.          .limit(1))
  7. # Retrieve multiple scalar values by calling scalar() with as_tuple=True.
  8. facid, nslots = query.scalar(as_tuple=True)

List the total slots booked per facility per month, part 2

Produce a list of the total number of slots booked per facility per month in the year of 2012. In this version, include output rows containing totals for all months per facility, and a total for all months for all facilities. The output table should consist of facility id, month and slots, sorted by the id and month. When calculating the aggregated values for all months and all facids, return null values in the month and facid columns.

Postgres ONLY.

  1. SELECT facid, date_part('month', starttime), SUM(slots)
  2. FROM booking
  3. WHERE date_part('year', starttime) = 2012
  4. GROUP BY ROLLUP(facid, date_part('month', starttime))
  5. ORDER BY facid, date_part('month', starttime)
  1. month = fn.date_part('month', Booking.starttime)
  2. query = (Booking
  3.          .select(Booking.facility,
  4.                  month.alias('month'),
  5.                  fn.SUM(Booking.slots))
  6.          .where(fn.date_part('year', Booking.starttime) == 2012)
  7.          .group_by(fn.ROLLUP(Booking.facility, month))
  8.          .order_by(Booking.facility, month))

List the total hours booked per named facility

Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted by facility id.

  1. SELECT f.facid, f.name, SUM(b.slots) * .5
  2. FROM facilities AS f
  3. INNER JOIN bookings AS b ON (f.facid = b.facid)
  4. GROUP BY f.facid, f.name
  5. ORDER BY f.facid
  1. query = (Facility
  2.          .select(Facility.facid, Facility.name, fn.SUM(Booking.slots) * .5)
  3.          .join(Booking)
  4.          .group_by(Facility.facid, Facility.name)
  5.          .order_by(Facility.facid))

List each member’s first booking after September 1st 2012

Produce a list of each member name, id, and their first booking after September 1st 2012. Order by member ID.

  1. SELECT m.surname, m.firstname, m.memid, min(b.starttime) as starttime
  2. FROM members AS m
  3. INNER JOIN bookings AS b ON b.memid = m.memid
  4. WHERE starttime >= '2012-09-01'
  5. GROUP BY m.surname, m.firstname, m.memid
  6. ORDER BY m.memid;
  1. query = (Member
  2.          .select(Member.surname, Member.firstname, Member.memid,
  3.                  fn.MIN(Booking.starttime).alias('starttime'))
  4.          .join(Booking)
  5.          .where(Booking.starttime >= datetime.date(2012, 9, 1))
  6.          .group_by(Member.surname, Member.firstname, Member.memid)
  7.          .order_by(Member.memid))

Produce a list of member names, with each row containing the total member count

Produce a list of member names, with each row containing the total member count. Order by join date.

Postgres ONLY (as written).

  1. SELECT COUNT(*) OVER(), firstname, surname
  2. FROM members ORDER BY joindate
  1. query = (Member
  2.          .select(fn.COUNT(Member.memid).over(), Member.firstname,
  3.                  Member.surname)
  4.          .order_by(Member.joindate))

Produce a numbered list of members

Produce a monotonically increasing numbered list of members, ordered by their date of joining. Remember that member IDs are not guaranteed to be sequential.

Postgres ONLY (as written).

  1. SELECT row_number() OVER (ORDER BY joindate), firstname, surname
  2. FROM members ORDER BY joindate;
  1. query = (Member
  2.          .select(fn.row_number().over(order_by=[Member.joindate]),
  3.                  Member.firstname, Member.surname)
  4.          .order_by(Member.joindate))

Output the facility id that has the highest number of slots booked, again

Output the facility id that has the highest number of slots booked. Ensure that in the event of a tie, all tieing results get output.

Postgres ONLY (as written).

  1. SELECT facid, total FROM (
  2.   SELECT facid, SUM(slots) AS total,
  3.          rank() OVER (order by SUM(slots) DESC) AS rank
  4.   FROM bookings
  5.   GROUP BY facid
  6. ) AS ranked WHERE rank = 1
  1. rank = fn.rank().over(order_by=[fn.SUM(Booking.slots).desc()])
  3. subq = (Booking
  4.         .select(Booking.facility, fn.SUM(Booking.slots).alias('total'),
  5.                 rank.alias('rank'))
  6.         .group_by(Booking.facility))
  8. # Here we use a plain Select() to create our query.
  9. query = (Select(columns=[subq.c.facid, subq.c.total])
  10.          .from_(subq)
  11.          .where(subq.c.rank == 1)
  12.          .bind(db))  # We must bind() it to the database.
  14. # To iterate over the query results:
  15. for facid, total in query.tuples():
  16.     print(facid, total)

Rank members by (rounded) hours used

Produce a list of members, along with the number of hours they’ve booked in facilities, rounded to the nearest ten hours. Rank them by this rounded figure, producing output of first name, surname, rounded hours, rank. Sort by rank, surname, and first name.

Postgres ONLY (as written).

  1. SELECT firstname, surname,
  2. ((SUM(bks.slots)+10)/20)*10 as hours,
  3. rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
  4. FROM members AS mems
  5. INNER JOIN bookings AS bks ON mems.memid = bks.memid
  6. GROUP BY mems.memid
  7. ORDER BY rank, surname, firstname;
  1. hours = ((fn.SUM(Booking.slots) + 10) / 20) * 10
  2. query = (Member
  3.          .select(Member.firstname, Member.surname, hours.alias('hours'),
  4.                  fn.rank().over(order_by=[hours.desc()]).alias('rank'))
  5.          .join(Booking)
  6.          .group_by(Member.memid)
  7.          .order_by(SQL('rank'), Member.surname, Member.firstname))

Find the top three revenue generating facilities

Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name.

Postgres ONLY (as written).

  1. SELECT name, rank FROM (
  2.     SELECT f.name, RANK() OVER (ORDER BY SUM(
  3.         CASE WHEN memid = 0 THEN slots * f.guestcost
  4.         ELSE slots * f.membercost END) DESC) AS rank
  5.     FROM bookings
  6.     INNER JOIN facilities AS f ON bookings.facid = f.facid
  7.     GROUP BY f.name) AS subq
  8. WHERE rank <= 3
  9. ORDER BY rank;
  1. total_cost = fn.SUM(Case(None, (
  2.     (Booking.member == 0, Booking.slots * Facility.guestcost),
  3. ), (Booking.slots * Facility.membercost)))
  5. subq = (Facility
  6.         .select(Facility.name,
  7.                 fn.RANK().over(order_by=[total_cost.desc()]).alias('rank'))
  8.         .join(Booking)
  9.         .group_by(Facility.name))
  11. query = (Select(columns=[subq.c.name, subq.c.rank])
  12.          .from_(subq)
  13.          .where(subq.c.rank <= 3)
  14.          .order_by(subq.c.rank)
  15.          .bind(db))  # Here again we used plain Select, and call bind().

Classify facilities by value

Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name.

Postgres ONLY (as written).

  1. SELECT name,
  2.   CASE class WHEN 1 THEN 'high' WHEN 2 THEN 'average' ELSE 'low' END
  3. FROM (
  4.   SELECT f.name, ntile(3) OVER (ORDER BY SUM(
  5.     CASE WHEN memid = 0 THEN slots * f.guestcost ELSE slots * f.membercost
  6.     END) DESC) AS class
  7.   FROM bookings INNER JOIN facilities AS f ON bookings.facid = f.facid
  8.   GROUP BY f.name
  9. ) AS subq
  10. ORDER BY class, name;
  1. cost = fn.SUM(Case(None, (
  2.     (Booking.member == 0, Booking.slots * Facility.guestcost),
  3. ), (Booking.slots * Facility.membercost)))
  4. subq = (Facility
  5.         .select(Facility.name,
  6.                 fn.NTILE(3).over(order_by=[cost.desc()]).alias('klass'))
  7.         .join(Booking)
  8.         .group_by(Facility.name))
  10. klass_case = Case(subq.c.klass, [(1, 'high'), (2, 'average')], 'low')
  11. query = (Select(columns=[subq.c.name, klass_case])
  12.          .from_(subq)
  13.          .order_by(subq.c.klass, subq.c.name)
  14.          .bind(db))

Recursion

Common Table Expressions allow us to, effectively, create our own temporary tables for the duration of a query - they’re largely a convenience to help us make more readable SQL. Using the WITH RECURSIVE modifier, however, it’s possible for us to create recursive queries. This is enormously advantageous for working with tree and graph-structured data - imagine retrieving all of the relations of a graph node to a given depth, for example.

Find the upward recommendation chain for member ID 27

Find the upward recommendation chain for member ID 27: that is, the member who recommended them, and the member who recommended that member, and so on. Return member ID, first name, and surname. Order by descending member id.

  1. WITH RECURSIVE recommenders(recommender) as (
  2.   SELECT recommendedby FROM members WHERE memid = 27
  3.   UNION ALL
  4.   SELECT mems.recommendedby
  5.   FROM recommenders recs
  6.   INNER JOIN members AS mems ON mems.memid = recs.recommender
  7. )
  8. SELECT recs.recommender, mems.firstname, mems.surname
  9. FROM recommenders AS recs
  10. INNER JOIN members AS mems ON recs.recommender = mems.memid
  11. ORDER By memid DESC;
  1. # Base-case of recursive CTE. Get member recommender where memid=27.
  2. base = (Member
  3.         .select(Member.recommendedby)
  4.         .where(Member.memid == 27)
  5.         .cte('recommenders', recursive=True, columns=('recommender',)))
  7. # Recursive term of CTE. Get recommender of previous recommender.
  8. MA = Member.alias()
  9. recursive = (MA
  10.              .select(MA.recommendedby)
  11.              .join(base, on=(MA.memid == base.c.recommender)))
  13. # Combine the base-case with the recursive term.
  14. cte = base.union_all(recursive)
  16. # Select from the recursive CTE, joining on member to get name info.
  17. query = (cte
  18.          .select_from(cte.c.recommender, Member.firstname, Member.surname)
  19.          .join(Member, on=(cte.c.recommender == Member.memid))
  20.          .order_by(Member.memid.desc()))