library(DBI)
library(RSQLite)
library(tidyverse)

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.5
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.5.1     ✔ tibble    3.2.1
## ✔ lubridate 1.9.4     ✔ tidyr     1.3.1
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(dbplyr)

## 
## Attaching package: 'dbplyr'
## 
## The following objects are masked from 'package:dplyr':
## 
##     ident, sql

con <- dbConnect(RSQLite::SQLite(), "data/chinook.sqlite")

top_artists <- dbGetQuery(con, "
  SELECT 
      ar.Name AS ArtistName,
      COUNT(al.AlbumId) AS AlbumCount
  FROM Artist AS ar
  INNER JOIN Album AS al ON ar.ArtistId = al.ArtistId
  GROUP BY ar.ArtistId, ar.Name
  ORDER BY AlbumCount DESC
  LIMIT 10;
")

top_artists

##         ArtistName AlbumCount
## 1      Iron Maiden         21
## 2     Led Zeppelin         14
## 3      Deep Purple         11
## 4        Metallica         10
## 5               U2         10
## 6    Ozzy Osbourne          6
## 7        Pearl Jam          5
## 8  Various Artists          4
## 9    Faith No More          4
## 10    Foo Fighters          4

ggplot(top_artists, aes(x = reorder(ArtistName, AlbumCount), y = AlbumCount)) +
  geom_col(fill = "firebrick") +
  coord_flip() +
  labs(
    title = "Top 10 Artists by Number of Albums in Chinook",
    subtitle = "Created with ggplot2 in R",
    x = "Artist",
    y = "Number of Albums"
  ) +
  theme_minimal()

dbDisconnect(con)