![image](https://gist.github.com/user-attachments/assets/4f7bf485-9258-48d5-8993-535dc8be3b3f)

# The queries

## Panel: Main Sheet
SQL:
```
SELECT title, author, copyright, href, image, rating, series
FROM main
```

## Panel: Genre Stats
SQL:
```
WITH genre_counts AS (
  SELECT asin, COUNT(*) AS genre_count
  FROM genres
  GROUP BY asin
),
joined AS (
  SELECT 
    g.asin,
    g.genre,
    m.runtimeLengthMin AS total_minutes,
    m.runtimeLengthMin / gc.genre_count AS weighted_minutes
  FROM main m
  JOIN genres g ON m.asin = g.asin
  JOIN genre_counts gc ON m.asin = gc.asin
)
SELECT 
  genre,
  COUNT(DISTINCT asin) AS book_count,
  ROUND(SUM(total_minutes) / 60.0, 1) AS total_hours_unweighted,
  ROUND(SUM(weighted_minutes) / 60.0, 1) AS total_hours_weighted
FROM joined
GROUP BY genre
HAVING book_count > 3
ORDER BY total_hours_unweighted DESC
```

## Panel: Author Stats
SQL:
```
WITH author_counts AS (
  SELECT asin, COUNT(*) AS author_count
  FROM authors
  GROUP BY asin
),
joined AS (
  SELECT 
    a.asin,
    a.author,
    m.runtimeLengthMin AS total_minutes,
    m.runtimeLengthMin / ac.author_count AS weighted_minutes
  FROM main m
  JOIN authors a ON m.asin = a.asin
  JOIN author_counts ac ON m.asin = ac.asin
)
SELECT 
  author,
  COUNT(DISTINCT asin) AS book_count,
  ROUND(SUM(total_minutes) / 60.0, 1) AS total_hours_unweighted,
  ROUND(SUM(weighted_minutes) / 60.0, 1) AS total_hours_weighted
FROM joined
GROUP BY author
HAVING book_count > 3
ORDER BY total_hours_unweighted DESC
```

## Panel: Hours Per Copyright Year
SQL:

```
SELECT
  CAST(CONCAT(
    COALESCE(
      copyright,
      EXTRACT(YEAR FROM releaseDate)
    ), '-01-01') AS DATETIME) AS time,
  ROUND(SUM(runtimeLengthMin) / 60.0, 1) AS total_hours
FROM main
GROUP BY time
ORDER BY time
```

## Panel: Series Stats

SQL:
```
SELECT 
  m.seriesPrimary_name AS series_name,
  COUNT(DISTINCT m.asin) AS book_count,
  ROUND(SUM(m.runtimeLengthMin) / 60.0, 1) AS total_hours,
  GROUP_CONCAT(DISTINCT a.author ORDER BY a.author SEPARATOR ', ') AS authors
FROM main m
JOIN authors a ON m.asin = a.asin
WHERE m.seriesPrimary_asin IS NOT NULL
GROUP BY series_name
ORDER BY book_count DESC, total_hours DESC
```

## Panel: Just the sheets
(No SQL expression; shows raw named ranges from Google Sheets directly.)


# 📚 Audible Library Dashboard – Project Summary

## ✅ 1. Scrape Audible Library  
Used browser DevTools console JavaScript to extract:
- Titles  
- ASINs  
- Authors, Series, and Links  
…directly from the Audible "My Library" HTML.

## ✅ 2. Enrich Data via API  
Wrote a Python script to enrich each ASIN by calling the **Audnexus API**:
- Added runtime, genres, narrators, release date, images, ISBN, and summary
- Output: a clean `audible_lib_enriched.json` file

## ✅ 3. Export to Multi-Table CSV  
Used Python to:
- Flatten array fields (authors, narrators, genres) into separate CSVs
- Normalize fields (e.g., `seriesPrimary_name`)
- Output multiple CSVs for import into **Google Sheets** with named ranges

## ✅ 4. Build a Grafana Dashboard Using SQL Expressions  
- Backed by **Google Sheets** as the data source
- Powered by **SQL Expressions (GMS engine)**:
  - Time series by release or copyright year
  - Runtime and book count by genre, author, and series
  - Weighted calculations for multi-author/genre relationships
  - CSV-style field aggregation with `GROUP_CONCAT`
  - Clean handling of joins and string normalization