Arcade 2025 Feburary Week 4 Help - mini lab: big query 3 and mini-lab: BigQuery 4

[weishiy]

Does anyone know how to solve the Arcade 2025 February Week 4 mini-lab: BigQuery 3 and mini-lab: BigQuery 4? I’ve tried multiple times but haven’t passed it yet.

Here is part of the requirements for mini-lab 3:

Overview
Labs are timed and cannot be paused. The timer starts when you click Start Lab.
The included cloud terminal is preconfigured with the gcloud SDK.
Use the terminal to execute commands and then click Check my progress to verify your work.
You are a data engineer and you are managing Google BigQuery, a data warehouse service that lets you store, manage, and analyze large datasets.

In this scenario, you have a pre-created dataset Google Cloud Platform. You are also provided the two tables with detailed schema and entries.

Your goal is to execute the query to select records that have matching values in both tables by using the fields from both tables. Once you have the data, save the results in a view with the name "Product_View".

You need to complete the following tasks:

Check the tables schemas and data that is created inside the Inventory Dataset.
Run the query to fetch the product_name and price after applying the INNER_JOIN on the products and category table where category_id = 1.
You should see a similar output:

|__product_name__ | __price__ |
| ------------- | -----------|
| Fitness Tracker | 80.0 |
| Portable Monitor | 200.0 |
| Smartphone | 800.0 |
| Smartwatch | 250.0 |
| Fitness Smartwatch| 150.0 |
| Monitor | 300.0 |
| Tablet | 600.0 |
| Laptop | 1200.0 |

[nunuvin]

Same thing. Reach out to support, they said they are investigating it...

[Sailor_Here]

Did they reply back again??

[nunuvin]

They made it optional for the arcade badge.

[RAOKS]

Yes, only mini lab BigQuery 3: optional and on 25th Feb 2025 Trivia badge generated, if remaining three labs completed. On 25th Feb 2025 , this lab is also rectified and I have uploaded working code for the same.

[dotiendung9997]

Same problem.

[RAOKS]

I hope below information is useful . I have found solution .

mini lab BigQuery-4 -solution

copy bucket name and run  one set of code at a time 

=======================================================

# Set the PROJECT_ID and REGION variables

export PROJECT_ID=$(gcloud config get-value project)
export REGION=$(gcloud compute project-info describe --format="value(commonInstanceMetadata.items[google-compute-default-region])")

#================================================================================
#copy cloud storage bucket name as per your lab.

export BUCKET_NAME=
#===============================================================
#Upload the csv data in your table from pre-created cloud storage bucket

bq load --source_format=CSV --autodetect products.products_information gs://$BUCKET_NAME/products.csv
#=========================================================================================================================

#Create a search index on the table_name table for all the columns.

bq query --use_legacy_sql=false "CREATE SEARCH INDEX IF NOT EXISTS products.p_i_search_index ON products.products_information (ALL COLUMNS);"
#==================================================================
#Run a query to search across all columns of the table_name table for the value 22 oz Water Bottle product and returns the #rows that contain this value using SEARCH method.

bq query --use_legacy_sql=false "SELECT * FROM products.products_information WHERE SEARCH(STRUCT(), '22 oz Water Bottle') = TRUE;"
#===================================================================================================================

#-------------------------------------------end of solution------------------------

[dotiendung9997]

Thanks you It's work!

What about mini lab : BigQuery : 3, Do you have new solution ? @RAOKS 

[edsfocci]

Thanks RAOKS, can you explain why your solution works and not mine (shown below)?

bq load \
--autodetect \
--source_format=CSV \
products.products_information \
gs://$BUCKET_NAME/products.csv

bq query --nouse_legacy_sql '
CREATE SEARCH INDEX
my_index
ON
products.products_information(ALL COLUMNS)'

bq query --nouse_legacy_sql '
SELECT
*
FROM
products.products_information
WHERE
SEARCH(products_information, "`22 oz Water Bottle`")'

[RAOKS]

Convert to single line code . and try your code.

[weishiy]

It works for mini-lab:big query 4. Very thanks！

[santoshkumar639]

 output code ?

[jacquip]

Mini Lab 3 seems to be an exercise in "guess what I'm thinking" for the validation. I have tried numerous different ways of writing the query, all of which produce the correct result, but are not accepted by the grader, e.g.

INNER JOIN vs just JOIN syntax

Changing the order of category.product_id = products.product_id and vice versa

Putting the condition category_id = 1 as either a WHERE condition or as part of the join, as a filter on either the category table or the products table

Getting a distinct list of results by either using DISTINCT or GROUP BY, or returning a non-distinct list of results

Doing the join first in a CTE and then selecting from the CTE where category = 1, or just writing it inline (a CTE is overkill here but I often see CTEs used where they are not actually needed in the name of "readability", so thought I would try it)

I am honestly questioning my sanity, I have been writing SQL for over 20 years (!) and this is pretty basic. 

I suspect the whole lab has been constructed in a bit of a rush given the error message returned by the validation: Please execute the desired qwery to fetch fetch the 'product_name' and 'price' after applying the `INNER_JOIN`.

[RAOKS]

*mini lab BigQuery 4-*I have found solution ,uploaded code and its working and updated  in community.

*mini lab BigQuery 3:*

I have tried different queries,as in lab sample output they have
mentioned __product_name__ , | __price__ . I have tried using AS.,
SELECT DISTINCT , INNER JOIN, ORDER BY
*Despite getting exact output what has been shown in the lab. Check Task
error displayed . *
mini bigquery 3 lab output -still check task error
One one more problem I have identified is table names

Under the project - its been mentioned as *Table1-Products* and*
Table2-Categories*.
But in the lab when I query the Inventory dataset to display tables,
actual tables are *products and category*.

Escalated to support team , clearly some configuration problem.
Thank you for sharing your inputs.

[weishiy]

Thank ROAKS！ 

Here are solution for big query 4:

# Set project and region variables
export PROJECT_ID=$(gcloud config get-value project)
export REGION=$(gcloud compute project-info describe --format="value(commonInstanceMetadata.items[google-compute-default-region])")

# Set your pre-created bucket name (update with your actual bucket name)
export BUCKET_NAME=projectID-bucket  ##replace project id

# Load CSV data into the BigQuery table
bq load --source_format=CSV --autodetect products.products_information gs://$BUCKET_NAME/products.csv

# Create a search index on all columns of the products_information table
bq query --use_legacy_sql=false "CREATE SEARCH INDEX IF NOT EXISTS products.p_i_search_index ON products.products_information (ALL COLUMNS);"

# Query the table to find rows containing "22 oz Water Bottle"
bq query --use_legacy_sql=false "SELECT * FROM products.products_information WHERE SEARCH(STRUCT(), '22 oz Water Bottle') = TRUE;"

[TharinduM]

i do the all the thinks but it this lab not completed what reason it ?

[RAOKS]

lab rectified, updated solution. check it

[Fuze11]

Try the below query for "mini lab : BigQuery : 3". It worked for me.

PROJECT_ID=`gcloud config get-value project`

bq ls --format=json $PROJECT_ID:Inventory

bq query --use_legacy_sql=false "
SELECT DISTINCT products.product_name, products.price
FROM \`$PROJECT_ID.Inventory.products\` AS products
INNER JOIN \`$PROJECT_ID.Inventory.category\` AS category
ON products.category_id = category.category_id
WHERE products.category_id = 1;"

bq query --use_legacy_sql=false "
CREATE VIEW \`$PROJECT_ID.Inventory.Product_View\` AS
SELECT DISTINCT products.product_name, products.price
FROM \`$PROJECT_ID.Inventory.products\` AS products
INNER JOIN \`$PROJECT_ID.Inventory.category\` AS category
ON products.category_id = category.category_id
WHERE products.category_id = 1;"

[RAOKS]

I have checked your code also its working. Good Job

One small suggestion , its not fully addressing first point

#Check the tables schemas and data that is created inside the Inventory Dataset.. 

Check Task done because mostly concentrating on second and third points in lab. 

[RAOKS]

mini lab BigQuery 3 rectified. Below code is working for me. I hope it will be helpful in solving lab.

#=====================================================================
# Set the PROJECT_ID

export PROJECT_ID=$(gcloud config get-value project)

#===================
#Check the tables schemas and data that is created inside the Inventory Dataset.

bq show --format=json Inventory.products && bq show --format=json Inventory.category && bq query --use_legacy_sql=false 'SELECT * FROM `Inventory.products` LIMIT 1000' && bq query --use_legacy_sql=false 'SELECT * FROM `Inventory.category` LIMIT 1000'
#======================================================================================================
#Run the query to fetch the unique product_name and price after applying the INNER_JOIN on the products and category table where category_id = 1.

bq query --use_legacy_sql=false 'SELECT DISTINCT products.product_name, products.price FROM `Inventory.products` AS products INNER JOIN `Inventory.category` AS category ON products.category_id = category.category_id WHERE products.category_id = 1;'
#=====================================================
#After executing the query, Save it as a VIEW named as: Product_View.

bq query --use_legacy_sql=false 'CREATE VIEW `Inventory.Product_View` AS SELECT DISTINCT products.product_name, products.price FROM `Inventory.products` AS products INNER JOIN `Inventory.category` AS category ON products.category_id = category.category_id WHERE products.category_id = 1;'
#==================================================================================================================