Calculate Requests Sent

Question

You are given a table containing a country code, the number of requests attempted, and the percentage of those requests that failed to sent. Calculate:

  1. The total number of requests attempted by country.
  2. The total number of failed requests attempted by country.

Answer

Create Table

-- Drop table if exists
DROP TABLE IF EXISTS global_usage

-- Create table called documents
CREATE TABLE global_usage (
    -- string variable
    country varchar(255),
    -- integer variable
    requests_attempted int,
    -- float variable
    percent_requests_failed float
);

Insert Data Into Table

-- Insert data into the table
INSERT INTO global_usage (country, requests_attempted, percent_requests_failed)
VALUES
    ('US', 100, .2),
    ('US', 100, .1),
    ('US', 100, .5),
    ('NZ', 100, .1),
    ('NZ', 100, .2),
    ('NZ', 100, .3),
    ('NZ', 100, .5);

View Table

-- View the raw table
SELECT * FROM global_usage;
countryrequests_attemptedpercent_requests_failed
US1000.2
US1000.1
US1000.5
NZ1000.1
NZ1000.2
NZ1000.3
NZ1000.5

Calculate Total Requests Attempted By Country

-- Select
SELECT
   -- the country
   country,
   -- the sum of requests attempted
   sum(requests_attempted)
-- From the global usage table
FROM global_usage
-- Aggregate the rows
GROUP BY
    -- by country
    country;
countrysum
US300
NZ400

Calculate Total Failed Requests By Country

-- Select
SELECT
   -- the country
   country,
   -- the sum of the number of requests attempted and the number of requests they were failed
   sum(requests_attempted * percent_requests_failed) as total_failed
-- from the table
FROM global_usage
-- aggregated by country
GROUP BY country
countrytotal_failed
US80
NZ110