Count combination + frequency

Question

I have such an assignment. I believe my guess is correct, however I didn't find anything confirming my assumption how frequency works with count function.

What was the most popular bike route (start/end station combination) in DC’s bike-share program over the first 3 months of 2012? How many times was the route taken?

• duration seconds: duration of the ride (in seconds)

• start time, end time: datetimes representing the beginning and end of the ride

• start station, end station: name of the start and end stations for the ride

This is the code I wrote, wanted to see if my guess regarding most popular route (i believe it is a frequency) is correct with COUNT combination.

If someone can confirm if my guess is right, I will appreciate.

Any such question should start with a **`CREATE TABLE`** statement. That's the canonical way of declaring the table definition. No amount of *talking about it* can make up for it. And *always* your version Postgres (though it seems mostly unimportant here). — Erwin Brandstetter, Nov 06 '21 at 04:07

score 0 · Answer 1 · answered Nov 06 '21 at 04:16

0

From description and the query look like ok if the start station and end station description are same for each and every station. however without looking into the table data it is little difficult to confirm.

answered Nov 06 '21 at 04:16

moin

31
6

score 0 · Accepted Answer · answered Nov 06 '21 at 04:16

SELECT start_station, end_station, count(*) AS ct_route_taken
FROM   tutorial.dc_bikeshare_q1_2012
GROUP  BY start_station, end_station
ORDER  BY ct_route_taken DESC
LIMIT  1;

Just count(*).

The name of the table would indicate that we need no WHERE clause.
If that's misleading and it covers a greater time interval, add a (proper!) WHERE clause like this:

WHERE  start_time >= '2012-01-01'
AND    start_time <  '2012-04-01'

Your query would eliminate most of '2012-03-31', since start_time is supposed to be a "datetime" type. Depending on which type exactly and where the date of "the first 3 months" is supposed to be located, we might need to adjust for time zone also.

See:

Count combination + frequency

2 Answers2

Linked