SQL Challenges

Challenge 1

Filter and Limit

Challenge: Write a query to find the top 10 countries with the highest number of outbound tourists in 2019.

Ingest the data from Kaggle in to BigQuery: Data Set

Issues: Too much data for BigQuery, over 5 million lines. Manually edited CSV to make it 400k lines containing the years of 2012 - 2014. Years 2017-2019 did not exist in this data set changed to 2012-2014
Keeping the same spread as the questions above, adjusted down. Gave the following: Outbound tourists (International tourism, number of departures) does not have a 2014 record, using 2013

                  
                  ,SELECT CountryName
                              ,IndicatorName
                              ,Year
                              ,ROUND(Value 2) AS Value1
                              ,
                  FROM `sql-project-372016.wdi.indicators`
                  WHERE IndicatorName LIKE "%departures" AND 
                               Year = 2013
                  ORDER BY Value DESC
                  LIMIT 10
                  
                

Challenge 2

Combine Data from Multiple Tables

Challenge: Write a query to retrieve the names and email addresses of all customers who have placed an order in the last 30 days, along with the name and price of the product they ordered.

Using 3 tables Customers, Products, and Orders with the below information. Orders is a list of dates with foreign keys from Customers and products linking them together. Each Customer and Product had a unique id. I joined each on those ids to link them to their order date.

                  
                  SELECT c.first
                              ,c.last
                              ,c.email
                              ,p.product
                              ,p.price
                              ,o.order_date
                              ,
                  FROM `sql-project-372016.customer_data.Customers` c
                  JOIN `sql-project-372016.customer_data.Orders` o
                  ON c.id= o.customer.id
                  JOIN `sql-project-372016.customer_data.Products` p
                  ON o.product_id= p.id
                  ORDER BY o.order_date
                  
                  

Challenge 3

Use a WITH Clause

Using a WITH clause, write a query to calculate the average revenue per day for the past 7 days. Assume you have a table Orders that contains order information with a revenue column and a date column.

Created a table with 33 items over 7 days with random amounts. Normally you can't aggregate average data in a normal query, so using a WITH CTE table to create a temp table and then pull info from that does.

                WITH daily_ave_rev AS (
                        SELECT DATE ( Date ) AS day
                            , AVG (Revenue) AS avg_revenue
                            ,
                        FROM `sql-project-372016.Orders.Orders`
                        GROUP BY day
                  )
                SELECT day
                    ,ROUND (ave_revenue,2)
                    ,
                FROM daily_ave_rev
                ORDER BY day
                
                

Challenge 4

Filter Results with INNER Query

Challenge: Write a query to retrieve the names and email addresses of all customers who have placed an order for a product with a price greater than $100 in the last 90 days.

This challenge required me to learn more about dates. And used 2 tables to Inner Join, person and order. The date needed to be 90 days in the past to “today” the end of the data set. The 2 filter options were date and cost, leaving us 1 row based on this data set.

                SELECT p.first_name
                              ,p.Last_name
                              ,p.email
                              ,o.item
                              ,o.Variable_Cost
                              ,o.date
                              ,
                  FROM `sql-project-372016.chal3.person` p
                  INNER JOIN `sql-project-372016.chal3.order` o
                  ON p.id= o.id
                  WHERE o.date >=  DATE_SUB (
                    (SELECT MAX(DATE)  FROM `sql-project-372016.chal3.order`) 
                    , INTERVAL 90 DAY)
                  AND o.Variable_Cost > 100
                
                

• github

Contact Info