How to join tables with non-equal condition?

Question

I’m trying to query all the users with their first successful app install with the app version details.

So I tried to query users’ first successful install, then compare the installed_date with latest app version before installed_date.

SELECT DISTINCT user_id, installed_time
WHERE state=SUCCESSFUL
FROM installation
ORDER BY ASC

The query above returns all users with their first successful installed.

How do I find which version of the app that they have installed when they first installed it successfully?

My tables are described below:

Installation:
user_id,
installed_date,

Version:
version_id,
release_date,
release_code,

Which database are you using? (MySQL, Postgres, Oracle, ...) — Andomar, Apr 27 '19 at 09:49

score 1 · Answer 1 · answered Apr 27 '19 at 12:06

I don't believe that this query does what you say:

SELECT DISTINCT user_id, installed_time
WHERE state=SUCCESSFUL
FROM installation
ORDER BY ASC;

Apart from the obvious syntax errors, users with multiple installs will appear multiple times. That may be what you want, but it is not the first successful install.

I think the query should be:

SELECT i.user_id, MIN(i.installed_time)
FROM installation i
WHERE i.state = 'SUCCESSFUL'
GROUP BY i.user_id;

Or, if you want all columns in the table, use DISTINCT ON:

SELECT DISTINCT ON (i.user_id) i.*
FROM installation i
WHERE i.state = 'SUCCESSFUL'
ORDER BY i.user_id, i.installation_time

If you want the valid version at that time, you have several choices. In Postgres, I would recommend a lateral join:

SELECT i.*, v.*
FROM (SELECT i.user_id, MIN(i.installed_time) as installed_time
      FROM installation i
      WHERE i.state = 'SUCCESSFUL'
      GROUP BY i.user_id
     ) i LEFT JOIN LATERAL
     (SELECT v.*
      FROM versions v
      WHERE v.release_date <= i.installed_time
      ORDER BY v.release_date DESC
      FETCH FIRST 1 ROW ONLY
     ) v
     ON 1=1;

joegalaxian · Answer 2 · 2019-04-27T10:01:40.753

You can easily achieve it by using window-functions. Window functions are extremely useful for "advanced" SQL scrips.

As usual in software, there are many ways of doing this, this is a simple one using FIRST_VALUE().

SELECT DISTINCT
  i.user_id,
  FIRST_VALUE(v.version_id) OVER(
    PARTITION BY i.user_id
    ORDER BY v.released_date DESC
  ) AS first_version_installed
FROM installation AS i
INNER JOIN version AS v
  ON i.installation_date >= v.released_date
WHERE i.state = 'SUCCESSFULL'

Further reading:

Window functions: http://www.postgresqltutorial.com/postgresql-window-function/

Andomar · Answer 3 · 2019-04-27T10:03:39.887

You can use distinct on (...) with an order by to get the version of the earliest installation. The distinct on expression must match the leftmost order by expression.

select  distinct on (i.user_id)
,       i.user_id
,       i.installed_date 
,       v.release_code
from    installation i
join    version v
on      v.release_date <= i.installed_date
where   i.state = 'SUCCESSFUL'
order by
        i.user_id              -- Required for distinct on, see note above
,       i.installed_date desc  -- First installation per user
,       v.release_date         -- Latest version available at installation date

How to join tables with non-equal condition?

3 Answers3