my_table
I have the following table which has the drivers and riders details captured. For each day( datetime
) there is one driver and zero or more riders. If there are more than one rider, for each rider the data ( name of rider and age of rider) is captured in a new row with the same datetime
. This may not be the right way to structure the data, but it is so primarily due to the varying number of riders per driver per datetime
id datetime driver age riders rider_name | rider_age
---|------------|--------|------|--------|------------|---
1 | 03/03/2009 | joe | 24 | 0 | |
2 | 04/03/2009 | john | 39 | 1 | juliet | 30
3 | 05/03/2009 | borat | 32 | 2 | jane | 45
4 | 05/03/2009 | | | | mike | 18
5 | 06/03/2009 | john | 39 | 3 | duke | 42
6 | 06/03/2009 | | | | jose | 33
7 | 06/03/2009 | | | | kyle | 24
required output
For each datetime value, need the driver, age, number of riders, name of youngest rider and number of riders within +/- 10 years of the driver
datetime driver age riders youngest_rider riders_within_ten_years_of_driver
------------|--------|------|--------|--------------|---
03/03/2009 | joe | 24 | 0 | | 0 # no rider
04/03/2009 | john | 39 | 1 | juliet | 1 # juliet
05/03/2009 | borat | 32 | 2 | mike | 0 # no rider
06/03/2009 | john | 39 | 3 | kyle | 2 # duke, jose
Aucun commentaire:
Enregistrer un commentaire