How would I do this in PRQL? - reservation database

[richb-hanover]

Here's a pretty complicated use case for PRQL. (And I imagine that it's a complicated SQL query as well). I ask your indulgence for helping me learn about this stuff.

I have a database that has information about several years of reservations at a hospitality business. The interesting table has two columns:

staydate - yyyy-mm-dd indicating the night a cabin was/will be occupied
resdate - yyyy-mm-dd indicating the date the reservation was made

The table is many-to-one: a guest who stays for a week has seven rows in the table (one for each night) with successive dates in the staydate column, and all seven resdate columns contain the date the reservation was made.

Example 1: When managing a hospitality business, it's interesting to know "What is this year's occupancy compared to the occupancy for previous years as of a certain date?" For example, what's the occupancy (total nights reserved) on February 1 or April 15 compared the same date in previous years? The query would get a count of all the rows where resdate <= the date in question, for each year that we have data, and the output would be something like:

As Of      Nights
2014-03-01   #
2015-03-01   #
2016-03-01   #
...
2023-03-01   #

Example 2: Rather than run the query "on-demand" (that is, for any particular date), it would be helpful to print a single table showing occupancy on the first and fifteenth of each month in the form:

Date    2014 2015 2016 2017 ... 2023
01-Jan    #    #    #    #   ...  #
15-Jan    #    #    #    #   ...  #
01-Feb    #    #    #    #   ...  #
15-Feb    #    #    #    #   ...  #
01-Mar    #    #    #    #   ...  #
15-Mar    #    #    #    #   ...  #
... etc...

My question: How could I structure a PRQL (or even SQL) query for either of these examples? I assume that I might need to provide a new table defining the interesting dates -"01-01", "01-15", "02-01", "02-15", ... and concatenate the relevant years. I also realize that the second example would wind up with a variable number of columns. I would be content with a solution that gives 24 columns (first and 15th of each month) for each year.

I assume that this could turn into an extremely complicated query, so I thank everyone in advance for their thoughts here.

I'm mentioning @max-sixty @snth @eitsupi @aljazerzen to see if they have thoughts. Please feel free to ignore if you have important work. Thanks again

Update: I changed the column names above to staydate and resdate to be consistent with the remainder of this thread

[max-sixty]

This was a great exercise! These are good examples.

it's interesting to know "What is this year's occupancy compared to the occupancy for previous years as of a certain date

I think PRQL could be quite helpful here at composability, since we can apply the window function to both the current occupancy relation and the one for the prior year.

let stays_prior = (
  from stays
  derive dt = stays.year - 1years
  # derive occupancy = occupancy_prior — edit: removed
)

func rolling_occupancy tbl -> (
  tbl
  group year (
    window expanding:true (
      select [dt, occupancy = (sum occupancy)]
    )
  )
)

from stays_r=(from stays | rolling_occupancy)
join stays_prior_r=(from stays_prior | rolling_occupancy) [==dt]
select [
  stays_r.dt, 
  occupancy_diff = stays_r.occupancy - stays_prior_r.occupancy
]

which compiles to:

WITH table_3 AS (
  SELECT
    dt,
    SUM(occupancy) OVER (
      PARTITION BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS _expr_0
  FROM
    stays
),
stays_prior AS (
  SELECT
    *,
    year - INTERVAL 1 YEAR AS date,
    occupancy_prior AS occupancy
  FROM
    stays
),
table_0 AS (
  SELECT
    dt,
    SUM(occupancy) OVER (
      PARTITION BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS occupancy
  FROM
    stays_prior
)
SELECT
  table_2.dt,
  table_2._expr_0 - table_1.occupancy AS occupancy_diff
FROM
  table_3 AS table_2
  JOIN table_0 AS table_1 ON table_2.dt = table_1.dt

-- Generated by PRQL compiler version:0.8.0 (https://prql-lang.org)

---

Example 2

Converting between rows & columns is admittedly not SQL's strongest point. Some DBs have built things to do this — e.g. https://duckdb.org/docs/sql/statements/pivot.html — but there isn't a standard way. What DB are you using? SQLite?

It can be done manually with a bunch of SELECT ... WHERE year = 2014 as 2014, but it's very verbose, and ossifies the columns in the query. PRQL would be moderately helpful here — we could use functions to avoid the repetition. But in-memory data packages like pandas or R are much better at handling rows & columns like this (and DBs are starting to copy them)

So I would suggest checking out what the DB offers and using that...

Happy to spend more time on this one if helpful

[richb-hanover]

This is terrific. I haven't internalized it all, but now I have an incentive to learn about window functions :-) I'll play around and get back if I have questions. Thanks!

[richb-hanover]

Sample Data... 2014-2015.csv

[richb-hanover]

Thanks again for your suggestions about queries for counting reservations. I have a few questions:

1. I am not sure that the solution you suggested solves my problem. Here's a query that gets part of the way to what I wanted: the number of nights in the "staydate" column that were reserved prior to a specific date, in this example, 1 Jan 2015:

   from reservations        # table with "staydate" and "resdate" columns
   filter (                 # only count nights stayed in 2015
     staydate >= "2015-01-01" && staydate<="2015-12-31"
   )
                            # that were reserved before 1 Jan 2015 
   filter resdate <= "2015-01-01"
   aggregate [
     count
   ]
   

2. It would be easy enough to fill out the rest of Example 1 by changing the second filter to successive years after 2015.

   But that's boring. I would far rather spend an hour figuring how to build a query to do this than spend 10 minutes iterating through the dates manually :-)

   How could I build a query that would group the number of nights reserved as of a specific date by the year? (The output table might look like the results of Example 1 above...)

3. Looking at the solution that @max-sixty presented, I still have some questions:

   • In let stays_prior = ..., where is occupancy_prior defined?
   • In the rolling_occupancy function, where is dt defined?
   • I dumped two years of anonymized data into the prior response

Thanks again.

[max-sixty]

   * In `let stays_prior = ...`, where is `occupancy_prior` defined?

Ah, this line actually isn't used. I commented it out above.

   * In the `rolling_occupancy` function, where is `dt` defined?

Sorry, I was using date and dt in different places. I've edited it above to only use dt.

   But that's _boring._ I would far rather spend an hour figuring how to build a query to do this than spend 10 minutes iterating through the dates manually :-)

This is an affliction of mine too!

   How could I build a query that would group the  number of nights reserved as of a specific date by the year? (The output table might look like the results of Example 1 above...)

But I do think that window functions do exactly this — i.e. what's the rolling sum within the year of every date prior to this date. Does it not?

[richb-hanover]

Hmmm... I think my data is different shape from what you describe. My table is two dates: reservedOn is the data a reservation was made; stayedOn is the actual date someone stayed. Multiple rows can have the same reservedOn

As I work through the suggestion above, it looks as if that solution relies on summing the occupancy column, which doesn't exist. Thoughts?

Update: For clarity, my example above and the sample data have columns of resdate and staydate not the names mentioned just now.

[max-sixty]

My suggestion didn't consider the exact name of the columns — sounds like occupancy is some sum of staydate grouped by date?

If you post a query that's close I'm happy to have a look!

[richb-hanover]

@max-sixty Sorry for the delayed response. I don't see how the rolling sum in your original suggestion will answer my question. (I don't entirely understand how it would work, but it seems to be comparing year-over-year values, when I want absolute numbers for each year. But perhaps I'm misunderstanding...)

Let me re-state my goal: I am aiming for a query that shows, "For any given year, how many nights were reserved prior to the first of each month for that year?"

This example computes the correct number of nights for a single resdate. I'd like to extend the query to handle 12 resdates (first of each month), and of course, then to extend it for each of the years I have data (2014 .. 2023)

Thanks again.

# this computes the number of nights stayed in 2015 that were reserved before 1Feb2015
from reservations        # table with "staydate" and "resdate" columns
filter (                 # only count nights stayed in 2015
  staydate >= "2015-01-01" && staydate<="2015-12-31"
)
# that were reserved before 1 Feb 2015 
filter resdate <= "2015-02-01"
aggregate [
  count
]