rush-hour-the-hard-way.md

Rush Hour the Hard Way

Payload Requests Business Logic

1. Write the ActiveRecord and SQL queries for average response time for all payload requests.

   PayloadRequest.average(:responded_in)

   SELECT avg(payload_requests.responded_in)
   FROM payload_requests;

2. Write the ActiveRecord and SQL queries for maximum response time for all payload requests.

   PayloadRequest.maximum(:responded_in)

    SELECT max(payload_requests.responded_in)
    FROM payload_requests;

3. Write the ActiveRecord and SQL queries for minimum response time for all payload requests.

   PayloadRequest.minimum(:responded_in)

    SELECT min(payload_requests.responded_in)
    FROM payload_requests;

4. What if we want a list of all the browser names in our user agents table?

   UserAgent.select(:browser).map(&:browser)

    SELECT user_agents.browser
    FROM user_agents

5. Now we have all the browsers that people used to make requests. But what if we had pre-populated browsers in our user_agents table that weren't actually being used in requests? If we want to find only the browsers actually used by the payload requests, we need to start at the payload_requests table, and then go retrieve browser names from the user_agents table.

Below is an N + 1 ActiveRecord query to find user agent names. As you can see, we are making more ActiveRecord requests with every iteration inside the map block. Coming from the perspective of the payload_requests table, write an Active Record query that returns an array of distinct user agent names without an N + 1 query:

*N + 1 Active Record Query:*
 ```ruby
 user_agent_id = PayloadRequest.distinct.pluck(:user_agent_id)
 user_agent_id.map do |id|
   UserAgent.find(id).browser
 end
 ```

*Using only a single Active Record query:*
 ```ruby
  PayloadRequest.select('DISTINCT user_agents.browser').joins(:user_agent).map(&:browser)
 ```
 
*Raw SQL query:*
 ```sql
  SELECT DISTINCT user_agents.browser
  FROM payload_requests
  INNER JOIN user_agents
  ON user_agents.id = payload_requests.user_agent_id;
 ```

1. Similar to the above, let's write a better ActiveRecord query for retieving the Operating Systems used in the requests:

   N + 1 Active Record Query:

   user_agent_id = PayloadRequest.distinct.pluck(:user_agent_id)
   user_agent_id.map do |id|
     UserAgent.find(id).os
   end

   Using only a single Active Record query:

   PayloadRequest.joins(:user_agent).select('DISTINCT user_agents.os').map(&:os)

   *Note: The order of the chaining ActiveRecord methods here doesn't matter, ActiveRecord will parse the whole thing to build out the appropriate SQL query. Here I have the joins method before select, in the previous example it was the other way around.

   Raw SQL query:

    SELECT DISTINCT user_agents.os
    FROM payload_requests
    INNER JOIN user_agents
    ON user_agents.id = payload_requests.user_agent_id;

2. TEMPLATE