SQL Quiz

Query Optimization: What is the main reason to not use SELECT * to explore your dataset?

• The BigQuery preview data table feature is faster and free to preview records

• Selecting all columns is an expensive operation performance-wise, especially with no filters

• Selecting all columns, even with WHERE clause filters, will scan your entire dataset and incur charges for all bytes processed.

• All of the above

Data Duplication Checking: What is a common way in SQL to identify duplicate records?

• Sort the records and look for multiple occurrences visually

• Use an easy-to-go COUNT function

• Use ranking functions (row_number, rank, dense_rank) to number the records in order of time

• Check whether the tables we are going to perform joining have the one-to-many relationship

Query Optimization: What is NOT one of the best practices for cost optimizing your queries?

• Avoid SELECTing all columns in your data, use only what you need

• Use LIMIT queries with WHERE clause filters to limit the amount of data scanned

• Filter your data as early as possible so you are not doing work on records that are later filtered out

• None of answer above

Query Optimization: What is the better method to quickly and approximately check A30 from `USER_LOGIN_V2` table?

• APPROX_COUNT_DISTINCT()

• Just use COUNT(DISTINCT)

• HyperLogLog++

• The first and third answer

What is better way to get the latest or highest value record? For example: user_id has highest gmv in a month of OTA?

• rank() over (partition by OTA order by gmv asc)

• rank() over (partition by OTA order by gmv desc)

• ARRAY_AGG(user_id order by gmv desc limit 1)[OFFSET(0)]

• row_number() over (partition by OTA order by gmv desc)

A data analyst wants to retrieve only records from a database that have matching values in two different tables. Which JOIN function should they use?

• LEFT JOIN

• RIGHT JOIN

• OUTER JOIN

• INNER JOIN

You are working with a dataset with the column name “firstquarterexpenses.” How can you rename this column to make it more readable?

• 1st_quarter_expenses

• fqe

• first_quarter_expenses

• first+quarter+expenses

You’re analyzing patient data for a healthcare company. During the data-validation process, you notice that the first date of service for some of the patients is later than the most recent date of service. Which type of data-validation check are you completing?

• Data structure

• Data consistency

• Data type

• Data range

During analysis, you complete a data-validation check for errors in customer identification (ID) numbers. Customer IDs must be eight characters and can contain numbers only. Which of the following customer ID errors will a data-type check help you identify?

• IDs that are repeated

• IDs in the wrong column

• IDs with more than eight characters

• IDs with text

Which of the action below could lead to data duplication?

• A user register his bank account for the first time.

• A user redeems 3 different vouchers in a day.

• A user fills in a registration form of promotion 2 times.

• A male user recognizes himself as female.