---
title: "BUSINES ANALYTICS FOUNDATINS INCLUDING THE R AND SQL"
format: html
editor: visual
---

# R & SQL

## Loading libraries

```{r setup, warning=FALSE, message=FALSE}
library(DBI)
library(RSQLite)
library(ggplot2)

```

## Connect to the Chinook SQLite database

```{r}
con <- dbConnect(RSQLite::SQLite(), dbname = "Chinook_Sqlite.sqlite")
```

## List all tables in the database

```{r}
tables <- dbListTables(con)
print(tables)
```

## Choose a Album table from the database

```{r}
query_album <- "SELECT * FROM Album LIMIT 10"
data_album <- dbGetQuery(con, query_album)
print(data_album)
```

## Query to get album details along with artist names

```{r}
query_album_artist <- "
SELECT Album.AlbumId, Album.Title AS AlbumTitle, Artist.Name AS ArtistName
FROM Album
JOIN Artist ON Album.ArtistId = Artist.ArtistId
LIMIT 10"

data_album_artist <- dbGetQuery(con, query_album_artist)
print(data_album_artist)
```

## Query to get the top-selling albums along with artist names

```{r}
query_top_selling_albums <- "
SELECT 
    Album.Title AS AlbumTitle, 
    Artist.Name AS ArtistName, 
    SUM(InvoiceLine.Quantity) AS TotalQuantitySold
FROM 
    InvoiceLine
JOIN 
    Track ON InvoiceLine.TrackId = Track.TrackId
JOIN 
    Album ON Track.AlbumId = Album.AlbumId
JOIN 
    Artist ON Album.ArtistId = Artist.ArtistId
GROUP BY 
    Album.AlbumId, Album.Title, Artist.Name
ORDER BY 
    TotalQuantitySold DESC
LIMIT 10"

# Execute the query
top_selling_albums <- dbGetQuery(con, query_top_selling_albums)
knitr::kable(top_selling_albums)
```

# Query to get the top-selling albums with the number of units sold, artist names, and total earnings

```{r}

query_top_selling_albums <- "
SELECT 
    Album.Title AS AlbumTitle, 
    Artist.Name AS ArtistName, 
    SUM(InvoiceLine.Quantity) AS TotalQuantitySold,
    SUM(InvoiceLine.Quantity * InvoiceLine.UnitPrice) AS TotalEarnings
FROM 
    InvoiceLine
JOIN 
    Track ON InvoiceLine.TrackId = Track.TrackId
JOIN 
    Album ON Track.AlbumId = Album.AlbumId
JOIN 
    Artist ON Album.ArtistId = Artist.ArtistId
GROUP BY 
    Album.AlbumId, Album.Title, Artist.Name
ORDER BY 
    TotalEarnings DESC
LIMIT 10"

# Execute the query
top_selling_albums <- dbGetQuery(con, query_top_selling_albums)
print(top_selling_albums)
```

# Sales Trends Over Time: Analyze how sales figures have changed over time to identify seasonal trends or growth patterns

```{r}
# Query monthly sales trends
query_monthly_sales <- "
SELECT 
    strftime('%Y-%m', Invoice.InvoiceDate) AS Month,
    SUM(InvoiceLine.Quantity) AS TotalQuantitySold
FROM 
    InvoiceLine
JOIN 
    Invoice ON InvoiceLine.InvoiceId = Invoice.InvoiceId
GROUP BY 
    strftime('%Y-%m', Invoice.InvoiceDate)
ORDER BY 
    Month;"
    
# Execute the query and fetch the data
monthly_sales <- dbGetQuery(con, query_monthly_sales)

# Plot the data
ggplot(monthly_sales, aes(x = Month, y = TotalQuantitySold)) +
    geom_line() +
    geom_point() +
    labs(title = "Monthly Quantity Sold",
         x = "Month",
         y = "Total Quantity Sold") +
    theme_minimal() +
    theme(axis.text.x = element_text(angle = 45, hjust = 1))
```