BigQuery SQL Query for top products from the Merchant Center

Question

I am running into an issue with writing an SQL query with Google Big Query. Basically looking to transfer the top products, per country, per category which are also in stock into a table.

So far I have pulled in the top products, per country, per category but the issue is with getting the 'in-stock' part added to the table. I can't find any similar keys in the schema to match them up.

Ideally the table would include:

Rank, Product Title, Country, Category, In-Stock

I would really appreciate any help on this! Thanks.

I have tried to add in a separate table that includes the 'availability' key for each product but I could not match it

Answer 1

You have your top_products table to check the rank that you can join to the product_inventory using the rank_id.
This join will retrieve the product_id and join that key to the products table.
After that, you get the availability information of the product and then you have all the information you require.