-- -- Test customer reviews dataset which is stored as a HDFS block. -- CREATE FOREIGN TABLE customer_reviews_hdfs_block ( 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/blk_-729487577044220672', max_error_count '2'); -- Does the average review rating change by product category? SELECT "product.category", round(avg("review.rating"), 2), count(*) FROM customer_reviews_hdfs_block 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)