SQL
# query <- '
# SELECT
# SUBSTRING("Date"::text FROM 1 FOR 4) AS year,
# COUNT(*) AS num_games
# FROM
# gamelogs
# GROUP BY
# year
# ORDER BY
# year;
# '
# dbGetQuery(con, query)
# using tidyverse
gamelogs |>
mutate(year = Date %>% str_sub(1, 4)) |>
count(year) |>
print(n = 30)
## # A tibble: 23 × 2
## year n
## <chr> <int>
## 1 1995 2017
## 2 1996 2267
## 3 1997 2266
## 4 1998 2432
## 5 1999 2428
## 6 2000 2429
## 7 2001 2429
## 8 2002 2426
## 9 2003 2430
## 10 2004 2428
## 11 2005 2431
## 12 2006 2429
## 13 2007 2431
## 14 2008 2428
## 15 2009 2430
## 16 2010 2430
## 17 2011 2429
## 18 2012 2430
## 19 2013 2431
## 20 2014 2430
## 21 2015 2429
## 22 2016 2428
## 23 2017 2430
# gamelogs object type
# class(gamelogs)
# gamelogs |>
# glimpse()
# number of rows in gamelogs
# query <- "
# SELECT COUNT(*)
# FROM gamelogs;
# "
#
# dbGetQuery(con, query)
nrow(gamelogs)
## [1] 55138