How would I do this in PRQL? - Most popular song

[richb-hanover]

@max-sixty @eitsupi I want to tune up the introduction.prql query. The current (0.9.0) file is kind of meaningless (I have no idea what "useful function" it performs.) I would like to demonstrate something "real" using the Chinook database. I have set my hat on this question:

What's the most popular song for each city in the database?

This query can illustrate joins, grouping, filtering, and other stuff in a seemingly premeditated way. I'm part-way there. So far, the query below isolates the city & country to show the count of the invoices.

But I need some kind of sub-query to count the t.names within each group, then sort by that count, then take 1.

How can I express this in PRQL? Thanks.

# A PRQL query begins with a table. Subsequent lines "transform (modify) it.
# The "output.sql" tab at right shows the SQL generated from this PRQL query.
# The "output.arrow" tab shows the result of the query.
from inv=invoices
join side:left item=invoice_items (==invoice_id) # join using column "invoice_id" in both tables
join side:left t=tracks (item.track_id==t.track_id) # or use separate "table.column"

group {inv.billing_city, inv.billing_country} (
  aggregate {
    ct = count this,
  }
)
sort inv.billing_city

[eitsupi]

I don't understand what kind of aggregation you want to do as I don't have the ER diagram in my head, is this what you want to do? (I haven't tried to see if this actually works)

from inv=invoices
join side:left item=invoice_items (==invoice_id)
join side:left t=tracks (item.track_id==t.track_id)
#filter inv.invoice_date >= @2010-01-01
#filter inv.total > 5
group {inv.billing_city, inv.billing_country} (
  aggregate {
    ct = count this,
  }
  sort {-ct}
  take 1
)

[richb-hanover]

Sorry - I should have made my request more explicit. My joins make a relation of:

inv.billing_city inv.billing_country ... t.track_name

For each group (billing_city, billing_country), I want to find the most "popular" track_name from all the orders that came in. I am guessing that I need to come up with a count of track_names within each group, then sort, then take the top 1. (NB: I used "track_name" for clarity; the column is really t.name in the Chinook database)

As a newcomer to all this, I'm not sure how to do it in SQL, but I imagine it'll be easier in PRQL :-) Thanks

[eitsupi]

Perhaps you are concerned that the number of rows will be reduced when performing aggregate functions (count)?
I think the general solution is to join the aggregated table with the pre-aggregated table.

In other words, in the case of PRQL, why not define the table before aggregation with let, then aggregation by count with let, and finally join the two in the main query?

I hope my answer is not out of point.

[richb-hanover]

Thanks for the speedy response. I need to think about your recommendation, but I'll be busy today and tomorrow, so I'll be back over the weekend.

[richb-hanover]

@eitsupi @max-sixty Thanks again for your thoughts. I have spent a bunch of time thinking about this, and looking at SQL examples. But I'm still having trouble translating those back to a PRQL representation.

I understand that you advise creating two queries with let, and then joining the result, but I don't understand the process of factoring into two queries. Would you help? Many thanks.

[richb-hanover]

@max-sixty @eitsupi @snth As I mentioned at yesterday's Dev Call, I would like to make the introduction.prql query more meaningful. As I stated, my goal is a query that answers What's the most popular song for each city in the database? while using most of the transforms available in PRQL.

The query in the original post (above) joins the invoices, invoice_items, and tracks tables, and then groups by billing_city (and billing_country) and displays the count of each group. Running this in the playground shows:

• Amsterdam has 38 titles
• Bangalore has 36
• etc.

I think what comes next is some kind of "inner query" that goes within the group transform. It would process each city's group to come up with the the count of each different track_id (or name), reverse-sort by that count, and take the first row.

How do I express this in prql? Thanks.

[snth]

Hi @richb-hanover ,

Let's start simple.

Top 5 most purchased tracks

from inv=invoices
join side:left item=invoice_items (==invoice_id) # join using column "invoice_id" in both tables
join side:left t=tracks (item.track_id==t.track_id) # or use separate "table.column"

group {Track=t.name} (
  aggregate {
    `Times Purchased` = sum item.quantity,
  }
)
sort {-`Times Purchased`}
take 5

Most popular tracks by city

This one didn't work out quite as I hoped.

First off, only 7 tracks were ever bought more than once in the same city (as per the following query)

from inv=invoices
join side:left item=invoice_items (==invoice_id) # join using column "invoice_id" in both tables
join side:left t=tracks (item.track_id==t.track_id) # or use separate "table.column"

group {Country=inv.billing_country, City=inv.billing_city, Track=t.name} (
  aggregate {
    `Times Purchased` = sum item.quantity,
  }
  #sort {-`Times Purchased`}
  #take 5
)
sort {-`Times Purchased`}
take 10

Now the commented out lines are there because I first tried to get the top track per city with the following but that didn't give me what I wanted. In particular the Country, City and Track are not included in the output. Should they not be though? I'm not sure if this isn't maybe a bug. I'll need to look at this again in the morning.

from inv=invoices
join side:left item=invoice_items (==invoice_id) # join using column "invoice_id" in both tables
join side:left t=tracks (item.track_id==t.track_id) # or use separate "table.column"

group {Country=inv.billing_country, City=inv.billing_city, Track=t.name} (
  aggregate {
    `Times Purchased` = sum item.quantity,
  }
  sort {-`Times Purchased`}
  take 1
)
sort {-`Times Purchased`}
take 10

HTH

[richb-hanover]

This helps a lot. I hadn't been able to get it to work right either, and I now know it's not (solely) me. Other thoughts:

1. It does stink that our data is so un-distinguished. It would have been nice if different cities had different numbers of orders, tracks, etc. just to make the reports more interesting.
2. When I was creating this example, I grouped on city and country to illustrate that you could have two columns in that tuple. Including the track means I can leave out the country from the example.
3. Ahah! Including the track in the group criteria seems to be part of the way to success. (I was trying to use it in some kind of inner-query.)
4. I think I have seen City and Track columns come out of a group - I'm not sure why they're not here now. More to investigate. Thanks again.

[richb-hanover]

I think that in item 3 above, I'm wrong - the track cannot be part of the outer city/country group. I submitted this as a question for number #3490 ...