Knowee
Questions
Features
Study Tools

In a travel tracking application, you need to generate a report on the distance traveled by each user to evaluate their activity.Your task is to write a query that retrieves this information, presenting the users in descending order of traveled distance. In case of a tie, the users should be ordered by name in ascending order for a clear and organized travel summary.The Sample records are given belowInput format :The input records are already prepopulated, as given in the problem statement.Output format :The output should display the list of users along with the total distance they have traveled, sorted in descending order of traveled distance. If a user has not taken any rides, their total distance traveled is considered as 0 as shown below.The output is ordered by travelled_distance in descending order and then by name in ascending order as shown below.name travelled_distanceElvis 450Lee 450Bob 317Jonathan 312Alex 222Alice 120Donald 0Refer to the sample output for the column headers.

Question

In a travel tracking application, you need to generate a report on the distance traveled by each user to evaluate their activity.Your task is to write a query that retrieves this information, presenting the users in descending order of traveled distance. In case of a tie, the users should be ordered by name in ascending order for a clear and organized travel summary.The Sample records are given belowInput format :The input records are already prepopulated, as given in the problem statement.Output format :The output should display the list of users along with the total distance they have traveled, sorted in descending order of traveled distance. If a user has not taken any rides, their total distance traveled is considered as 0 as shown below.The output is ordered by travelled_distance in descending order and then by name in ascending order as shown below.name travelled_distanceElvis 450Lee 450Bob 317Jonathan 312Alex 222Alice 120Donald 0Refer to the sample output for the column headers.

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution

The language of the text is SQL (Structured Query Language). Here is the SQL query to solve the problem:

SELECT name, 
       COALESCE(SUM(distance), 0) AS travelled_distance
FROM Users
LEFT JOIN Rides ON Users.id = Rides.user_id
GROUP BY name
ORDER BY travelled_distance DESC, name ASC;

Here is the step by step explanation:

  1. SELECT name, COALESCE(SUM(distance), 0) AS travelled_distance: This line is selecting the name from the Users table and the sum of distance from the Rides table for each user. If a user has no rides (and therefore no distance), the COALESCE function will return 0.

  2. FROM Users: This line is specifying the Users table as the main table to select from.

  3. LEFT JOIN Rides ON Users.id = Rides.user_id: This line is joining the Rides table to the Users table based on the user's id. A left join is used to include all users, even if they have no corresponding entry in the Rides table.

  4. GROUP BY name: This line is grouping the results by the user's name.

  5. ORDER BY travelled_distance DESC, name ASC;: This line is ordering the results first by the total travelled distance in descending order, and then by the user's name in ascending order.

This problem has been solved

Similar Questions

Write a query to display person name,contact number and balance  of all the details, who are travelled maximum number of times. If there is multiple record dipsplay the record in descending order by person name.Note:1. Display only the  peron name, contact number and balance whose travelled maximum number of times.2. Order by is mandatory.3. Compare your executed result with the sample output given below.Sample output: Submit

Write a query to display the person name,contact number,balance,entry_time and exit_time details of all the persons who is having the second highest balance in their travel cards. Display the records in ascending order based on their person_name.Note:1. Display only the below 5 columns,     i. person name     ii. contact number      iii. balance     iv. entry time and      v. exit timewith the condition having second highest balance.2. Order by is mandatory.3. Compare your executed result with the sample output given below.

Write a query to display the person_name and contact_number from the 'travel_card'.Display the records in ascending order based on the person_name.Note:1. Display only the column named person_name and contact_number present in the travel_card table.2. Order by is mandatorySample output: [Only few records are shown here]

Write a query to display the person_name and the contact_number from travel_card table of every person whose destination station is on the route 'North East line' with the payment more than 25. Display the records in ascending order based on person name.Note:1. Display only the person_name and contact_number from the travel_card table whose travel_payment destination route North East line with amount >25.2. Tables involved are travel_card, travel_payment and station3. In travel_payment table, the exit_station_id must match with the station id with name ‘North East line’ and amount greater than 25.4. Order by is mandatory.5. Compare your executed result with the sample output given below.

Write a query to display travel_card_id, entry_time and exit_time from the travel_payment.Display the records in ascending order based on the entry_time.Note:1. Display only the column named travel_card_id, entry_time and exit_time present in the travel_payment table.2. Order by is mandatorySample output: [Only few records are shown here]

1/2

Upgrade your grade with Knowee

Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.