Sql Query, Average Climbed And Pair That Has Climbed The Most Peaks

My Databases look like so: PEAK (NAME, ELEV, DIFF, MAP, REGION) CLIMBER (NAME, SEX) PARTICIPATED (TRIP_ID, NAME) CLIMBED (TRIP_ID, PEAK, WHEN) PEAK gives info about the mountain

Solution 1:

Question 1: For total number of trips (including every time a peak was climbed)

SELECT t1.sex, AVG(t1.peak_count) AS average
FROM
    (SELECT sex, COUNT(trip_id) AS peak_count
     FROM climber c LEFT JOIN  participated p ON c.name = p.name GROUPBY c.name, c.sex) t1

For each time a UNIQUE peak was climbed:

SELECT t1.sex, AVG(t1.peak_count) AS average
FROM
    (SELECT sex, COUNT(trip_id) AS peak_count
     FROM climber c LEFT JOIN  participated p ON c.name = p.name GROUPBY c.name, c.sex) t1

Question 2:

SELECT P1.Name, P2.Name, COUNT(DISTINCT p1.trip_id) AS trips
FROM participated p1 INNERJOIN  participated p2 ON p1.trip_id = p2.trip_id
WHERE p1.name > p2.name -- > instead of <> gets only one of the pairsGROUPBY P1.Name, P2.Name 
HAVINGCOUNT(DISTINCT p1.trip_id) >0ORDERBY trips DESC

Question 3:

SELECT p.name, cl.whenAS span_begin_date, DATEADD(day, 60, cl.when) AS span_end_date, count(c2.trip_id) AS peaks
FROM climbed cl LEFT JOIN 
climbed c2 ON c2.when BETWEEN cl.whenAND DATEADD(day, 60, cl.when)
GROUPBY p.name, cl.when, DATEADD(day, 60, cl.when)
HAVING COUNT(c2.trip_id) > 20ORDERBY peaks

Solution 2:

Here is my solution. If you provide sample data, this can be verified. For question 3, the some 60 day span is not clear. Can you please specify better?

Question 1

select x.sex, avg(x.peaks_escalated) as peaks
from (
    select u.name, u.sex, count(distinct c.peak) as peaks_escalated
    from t1_climbed c 
         inner join t1_participated p on c.trip_id = p.trip_id 
         inner join t1_climber u on p.name = u.name
    groupby u.name, u.sex ) x
groupby x.sex

Question 2

with list1 as (
select u.name as member, c.trip_id, c.peak, c.whenfrom t1_climbed c 
     inner join t1_participated p on c.trip_id = p.trip_id 
     inner join t1_climber u on p.name = u.name
)
select a.member as m1, b.member as m2, count(distinct a.peak) as total
from list1 a inner join list1 b 
            on a.trip_id = b.trip_id 
            and a.peak = b.peak 
            and a.when = b.whenand a.member <> b.member
groupby a.member, b.member

Solution 3:

Oracle Setup:

CREATETABLE PEAK (
  NAME VARCHAR2(50) PRIMARY KEY,
  ELEV INT,
  DIFF INT,
  MAP  VARCHAR2(10),
  REGION VARCHAR2(10)
);

CREATETABLE CLIMBER (
  NAME VARCHAR2(50) PRIMARY KEY,
  SEX  CHAR(1) CHECK ( SEX IN ( 'M', 'F' ) )
);

-- Created this to have a primary key    CREATETABLE TRIPS (
  TRIP_ID INTPRIMARY KEY
);

CREATETABLE PARTICIPATED (
  TRIP_ID INTREFERENCES TRIPS( TRIP_ID ),
  NAME  VARCHAR2(50) REFERENCES CLIMBER( NAME ),
  PRIMARY KEY ( TRIP_ID, NAME )
);

CREATETABLE CLIMBED (
  TRIP_ID INTREFERENCES TRIPS( TRIP_ID ),
  PEAK    VARCHAR2(50) REFERENCES PEAK ( NAME ),
  "WHEN"  DATE
);

Question 1

SELECT sex,
       AVG( num_peaks ) AS avg_peaks
FROM   (
  SELECT c.*,
         COUNT( DISTINCT l.peak ) num_peaks
  FROM   CLIMBED l
         INNERJOIN
         PARTICIPATED p
         ON ( p.trip_id = l.trip_id )
         RIGHTOUTERJOIN
         CLIMBER c
         ON ( p.name = c.name )
  GROUPBY c.name, c.sex
)
GROUPBY sex;

You need to OUTER JOIN climbers as they could have not participated in any trips (so having climbed 0 peaks) and this needs to be taken into account in the average. It is also possible that a person could have climbed a peak multiple times - when you want the number of peaks climbed by a person you want to exclude multiple climbs on the same peak and will need to use COUNT( DISTINCT ... ) (or another similar technique) - if you want to count multiple climbs then remove the DISTINCT keyword.

Question 2:

SELECT *
FROM   (
  SELECT  name1,
          name2,
          COUNT( DISTINCT c.peak ) AS num_peaks_climbed
  FROM    (
            SELECT  p1.name AS name1,
                    p2.name AS name2,
                    p1.trip_id
            FROM    PARTICIPATED p1
                    INNER JOIN
                    PARTICIPATED p2
                    ON ( p1.trip_id = p2.trip_id AND p1.name < p2.name )
          ) p
          INNER JOIN
          climbed c
          ON ( p.trip_id = c.trip_id )
  GROUPBY name1, name2
  ORDERBY num_peaks_climbed DESC
)
WHERE ROWNUM = 1;

Question 3:

SELECT*FROM   (
  SELECT p.name,
         COUNT( c.peak ) OVER ( PARTITIONBY p.name
                                ORDERBY c."WHEN"
                                RANGEBETWEENINTERVAL'-60'DAY PRECEDING
                                          ANDCURRENTROW
                              ) AS num_peaks_in_60_days,
         c."WHEN" AS last_date_of_range
  FROM   PARTICIPATED p
         INNERJOIN
         climbed c
         ON ( p.trip_id = c.trip_id )
)
WHERE  num_peaks_in_60_days >20;