title: Analytical Function for Querys
body: <h2>I am working on a scenario for one of my projects at work I don't have any tables or data to fill those tables but I have to write a hypothetical query-</h2>
  
  <p>i wrote some of the query with regulaur sql but i think analytical functions come into play for part 2 </p>
  
  <p>the database supposedly gets 50 million records of flying cars a day and each record logs the position of a car every second for the entire day</p>
  
  <p>i need to write a query that identifies any speeders speeding lets say will be anything over 500 miles per hour</p>
  
  <p>and then i need to write another query that makes sure cars keep a distance of 50 feet from any other flying car so i have to identify anyone within 50 feet of each other anytime in the day thats airborne [ 50 feet altitude ] etc..</p>
  
  <p>after i also need to write a query to ticket top 5% of speeders, assuming max speed within the city (including highways) is 50mph.
  the information i have to write the query with is GPS timestamp, GPS latitude and longitude of camera, and car license plate.</p>
  
  <p>here is what i have so far</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION speed(lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT, t time) RETURNS FLOAT AS $$
  DECLARE                                                   
      x float = 69.1 * (lat2 - lat1);                           
      y float = 69.1 * (lon2 - lon1) * cos(lat1 / 57.3);
      d float = sqrt(x * x + y * y);  
      tcon float = extract(hour from t);     
  BEGIN
     RETURN d/tcon;
  END  
  $$ LANGUAGE plpgsql;
  
  insert into report
      values (1,'04:23:20', 40.23,40.78,1200,1);
  insert into report
      values(2,'03:21:00', 89.12,-120.56,1300,1);
  insert into report
      values(3,'18:16:18', 30.12,-4.56,1400,1);
  insert into report
      values (1,'04:56:00', 75.23,152.78,1200,2);
  insert into report
      values(2,'06:14:00', 89.12,-12.56,1300,2);
  insert into report
      values(3,'19:21:10', 60.12, 15.42,1400,2);
  select report.carid,speed ((select(report.lat from report where carid = carid and entrynum = entrynum),
                  select(report.lon from report where carid = carid and entrynum = entrynum),
                  select(report.lat from report where carid = carid and entrynum = entrynum+1),
                  select(report.lon from report where carid = carid and entrynum = entrynum+1),
                  select(report.thours from report where carid = carid and entrynum = entrynum+1)
                  from report)) as s
  </code></pre>
  
  <p>I dont know what to do next</p>