I have to write Hive SQL.
I want to find the lowest price for each catalog. I want to get one row for each catalog with the catalog ID, the product ID, the price (lowest price), the image url, the large_ctgr_id, the small_ctgr_id, the large_ctgr_name, and the small_ctgr_name columns. In the example below, we finally need two rows.
In addition, there are multiple products in a catalog, and each product is classified into a large category and a small category.
Eventually we have to join the three tables.
There is a similar issue with the link I asked. (Finding the lowest price for each category using join sql in Hive) In this case, we joined only two tables, and the names of the columns changed slightly.
Help me. Thank you.
"catalog_product_match" table
catalog_id product_id
1001 500001
1001 500002
1002 500101
1002 500102
1002 500103
"product_info" table
prd_id price img_url large_ctgr_id small_ctgr_id
500001 29000 /app/url/img/500001.jpg 200 34543
500002 29500 /app/url/img/500002.jpg 201 2345234
500101 8100 /app/url/img/500101.jpg 1020 23252
500102 8100 /app/url/img/500102.jpg 42133 2349823
500103 8500 /app/url/img/500103.jpg 3435 342514
"category_info" table
l_ctgr_id s_ctgr_id l_ctgr_name s_ctgr_name
200 34543 computer notebook
200 3423984 computer tablet
201 2345234 phone smartphone
1020 23252 clothes top
42133 2349823 cup cup
3435 342514 pen ink
