--
-- Test customer reviews dataset queries.
--
CREATE FOREIGN TABLE customer_reviews
(
    customer_id TEXT not null,
    "review.date" DATE not null,
    "review.rating" INTEGER not null,
    "review.votes" INTEGER,
    "review.helpful_votes" INTEGER,
    "product.id" CHAR(10) not null,
    "product.title" TEXT not null,
    "product.sales_rank" BIGINT,
    "product.group" TEXT,
    "product.category" TEXT,
    "product.subcategory" TEXT,
    similar_product_ids CHAR(10)[]
)
SERVER json_server
OPTIONS(filename '@abs_srcdir@/data/customer_reviews_1998.1000.json.gz');
-- How people rate your products?
SELECT
    extract(month from "review.date") AS review_month,
    round(avg("review.rating"), 2),
    count(*)
FROM
    customer_reviews
GROUP BY
    review_month
ORDER BY
    review_month;
 review_month | round | count 
--------------+-------+-------
            1 |  4.48 |   224
            2 |  4.42 |   149
            6 |  4.50 |     2
            7 |  4.63 |    71
            8 |  4.61 |    75
            9 |  4.57 |   101
           10 |  4.42 |   130
           11 |  4.59 |   143
           12 |  4.54 |   105
(9 rows)

-- Do we have a correlation between a book's title's length and its review ratings?
SELECT
    width_bucket(length("product.title"), 1, 50, 5) title_length_bucket,
    round(avg("review.rating"), 2) AS review_average,
    count(*)
FROM
   customer_reviews
WHERE
    "product.group" = 'Book'
GROUP BY
    title_length_bucket
ORDER BY
    title_length_bucket;
 title_length_bucket | review_average | count 
---------------------+----------------+-------
                   1 |           4.50 |   135
                   2 |           4.48 |   364
                   3 |           4.53 |   190
                   4 |           4.52 |   151
                   5 |           4.60 |    99
                   6 |           4.62 |    55
(6 rows)

-- Does the average review rating change by product category?
SELECT
    "product.category",
    round(avg("review.rating"), 2),
    count(*)
FROM
    customer_reviews
GROUP BY
    "product.category"
ORDER BY
    count(*) DESC, "product.category"
LIMIT 20;
     product.category      | round | count 
---------------------------+-------+-------
 Science Fiction & Fantasy |  4.44 |   189
 Literature & Fiction      |  4.62 |   149
 Mystery & Thrillers       |  3.79 |    71
 Books on Tape             |  4.75 |    65
 Children's Books          |  4.49 |    65
 Nonfiction                |  4.56 |    57
 Religion & Spirituality   |  4.67 |    52
 Science                   |  4.47 |    36
 Health, Mind & Body       |  4.97 |    30
 Computers & Internet      |  4.50 |    26
 Horror                    |  4.32 |    25
 Business & Investing      |  4.57 |    21
 Biographies & Memoirs     |  4.55 |    20
 History                   |  4.45 |    20
 Teens                     |  4.26 |    19
 Entertainment             |  4.89 |    18
 Home & Garden             |  4.24 |    17
 Reference                 |  4.71 |    14
 Romance                   |  4.77 |    13
 Sports                    |  4.50 |    10
(20 rows)