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.

A specific radius of what? Sample code would help us understand what you are trying to achieve. Do you have something that is even working partly? — Gordon Linoff
– Gordon Linoff, Commented Jun 18, 2015 at 11:33

shA.t · Accepted Answer · 2015-06-21 04:27:20Z

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;

edited Jun 21, 2015 at 4:27

answered Jun 18, 2015 at 11:13

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

[ and ] are invalid in an SQL identifier. You need to use " for quoted identifiers.

user1798707 Over a year ago

Thank you for your answer. It seems to be working, but it would be really helpful if you could tell me how to find the BusinessID's for a specific time. In the example I want to find only the businesses which have check-ins in 14 hour and subtract with the 15 hour. I don't want to get all the businesses with one query.

shA.t Over a year ago

@user1798707 I add WHERE statements to answer, you can filter your time and hour in some condition on table2 like "day" = 1 AND "time" =14 ;).

Collectives™ on Stack Overflow

SQL: Complex query with subtraction from different cells

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related