SQL: Complex query with subtraction from different cells

Question

I have two tables and I want to combine their data.

The first table

+------------+-----+------+-------+
| BusinessID | Lat | Long | Stars |
+------------+-----+------+-------+
| abc123     |  32 |   74 | 4.5   |
| abd123     |  32 |   75 | 4     |
| abe123     |  33 |   76 | 3     |
+------------+-----+------+-------+

The second table is:

+------------+-----+------+-------+
| BusinessID | day | time | count |
+------------+-----+------+-------+
| abc123     |   1 |   14 |     5 |
| abc123     |   1 |   15 |     6 |
| abc123     |   2 |   13 |     1 |
| abd123     |   4 |   12 |     4 |
| abd123     |   4 |   13 |     8 |
| abd123     |   5 |   11 |     2 |
+------------+-----+------+-------+

So what I want to do is find all the Businesses that are in a specific radius and have more check ins in the next hour than the current.

So the results are

+------------+
| BusinessID |
+------------+
| abd123     |
| abc123     |
+------------+

Because they have more check-ins in the next hour than the previous (6 > 5, 8 > 4)

What is more it would be helpful if the results where ordered by their difference in check-ins number. Ex. ( 8 - 4 > 6 - 5 )

SELECT *
FROM table2 t2
WHERE t2.BusinessID IN (
    SELECT t1.BusinessID 
    FROM table1 t1
    WHERE earth_box(ll_to_earth(32, 74), 4000/1.609) @> ll_to_earth(Lat, Long)
    ORDER by earth_distance(ll_to_earth(32, 74), ll_to_earth(Lat, Long)), stars DESC
    ) AND checkin_day = 1 AND checkin_time = 14;

From the above query I can find the businesses in a radius and then find their check-ins in the specified time. Ex. 14. What I need to do now is to find the number of check-ins in the 15 hour (of the same businesses) and find if the number of the check-ins is greater than it was in the previous time.

Answer 1

I think you want something like this:

SELECT
    t1.BusinessID
FROM
    table1 t1
    JOIN
    (SELECT
        *,
        "count" - LAG("count") OVER (PARTITION BY BusinessID, "day" ORDER BY "time") "grow"
    FROM 
        table2
    WHERE
        /* Some condition on table2 */) t2
    ON t1.BusinessID = t2.BusinessID AND t2.grow > 0
WHERE
        /* Some condition on table1 */
ORDER BY
    t2.grow DESC;