Need help for writing a complex SQL query

complex sql queries
best way to write sql queries
mastering sql queries
advanced sql queries
etl complex queries
how to write efficient sql queries
how to write long sql queries
sql query tips

I need some help with a complex SQL query. Here's my setup: I have two tables USER_TABLE and USER_VISIT_STAT. USER_TABLE have columns like ID,USER_NAME,UID,DEPARTMENT and USER_VISIT_STAT have columns like ID,USERID,LAST_VISIT_DATE. USERID field of USER_VISIT_STAT table holds the USER_TABLE.ID.

When a user logs in to the application his current date is saved in USER_VISIT_STAT table (refer the screenshot). So I need to get the user details who are not connected to the application since last 2 years. But we have to take care of the UID. (means you can see in USER_TABLE there are two entries with same UID but case is different. In USER_VISIT_STAT u can see 101 LAST_VISIT_DATE is 31-08-18 and 103 LAST_VISIT_DATE is 20-01-16. So you have to ignore both the ids because user is same and he is active). In response table I need user name, user uid, user department and user max(last active date).

+--------------------------------------+
|             USER_TABLE               |
+--------------------------------------+
| ID    | NAME    | UID   | DEPARTMENT |
+--------------------------------------+
| 101   | PRAKASH | US45  | ENGG       |
| 102   | RAJESH  | US22  | ENGG       |
| 103   | PRAKASH | us45  | HR         |
| 104   | HARI    |  US9  | ENGG       |
| 105   | MAYANK  | US90  | HR         |
+--------------------------------------+
+--------------------------------+
|          USER_VISIT_STAT       |
+--------------------------------+
| ID | USID | MAX(LAST_VISIT_DATE)|
+--------------------------------+
| 1  | 101 |    31-08-18         |
| 2  | 102 |    30-08-18         |
| 3  | 101 |    30-08-18         |
| 4  | 103 |    20-01-16         |
| 5  | 104 |    29-08-16         |
| 6  | 105 |    19-07-16         |
| 7  | 101 |    12-06-16         |
| 8  | 102 |    12-06-16         |
| 9  | 104 |    13-04-16         |
+--------------------------------+
+-------------------------------------------------+
|                    RESPONSE                     |
+-------------------------------------------------+
| NAME   | UID  |   DEPARTMENT | LAST_VISIT_DATE  |
+-------------------------------------------------+
| HARI   | US9  |   ENGG       |    29-08-16      |
| MAYANK | US09 |   HR         |  19-07-16        |
+-------------------------------------------------+

Using an analytic form of the MAX() function, partitioning that by the upper case of the UID will solve for this. Available as a demo here SQL Fiddle

Query 1:

SELECT
  M.LAST_VISIT_DATE, U.*
FROM (
    SELECT
      U."UID" 
    , V.LAST_VISIT_DATE
    , MAX(V.LAST_VISIT_DATE) OVER(PARTITION BY UPPER(U."UID")) MAX_VISIT
    FROM USER_TABLE U
    INNER JOIN USER_VISIT_STAT V ON U.ID = V.USID
    ) M
INNER JOIN USER_TABLE U ON M."UID" = U."UID"
WHERE M.MAX_VISIT < ADD_MONTHS(SYSDATE,-24)
AND M.LAST_VISIT_DATE = M.MAX_VISIT

Results:

|       LAST_VISIT_DATE |  ID |   NAME |  UID | DEPARTMENT |
|-----------------------|-----|--------|------|------------|
| 2016-08-29 00:00:00.0 | 104 |   HARI |  US9 |       ENGG |
| 2016-07-19 00:00:00.0 | 105 | MAYANK | US90 |         HR |

Oracle 11g R2 Schema Setup:

CREATE TABLE USER_TABLE
    ("ID" int, "NAME" varchar2(7), "UID" varchar2(4), "DEPARTMENT" varchar2(4))
;

INSERT ALL
    INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
         VALUES (101, 'PRAKASH', 'US45', 'ENGG')
    INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
         VALUES (102, 'RAJESH', 'US22', 'ENGG')
    INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
         VALUES (103, 'PRAKASH', 'us45', 'HR')
    INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
         VALUES (104, 'HARI', 'US9', 'ENGG')
    INTO USER_TABLE ("ID", "NAME", "UID", "DEPARTMENT")
         VALUES (105, 'MAYANK', 'US90', 'HR')
SELECT * FROM dual
;

CREATE TABLE USER_VISIT_STAT       
    ("ID" int, "USID" int, "LAST_VISIT_DATE" timestamp)
;

INSERT ALL 
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (1, 101, '31-Aug-2018 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (2, 102, '30-Aug-2018 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (3, 101, '30-Aug-2018 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (4, 103, '20-Feb-2016 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (5, 104, '29-Aug-2016 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (6, 105, '19-Jul-2016 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (7, 101, '12-Jun-2016 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (8, 102, '12-Jun-2016 12:00:00 AM')
    INTO USER_VISIT_STAT        ("ID", "USID", "LAST_VISIT_DATE")
         VALUES (9, 104, '13-Apr-2016 12:00:00 AM')
SELECT * FROM dual
;

Learn the Three Crucial Steps to Writing Better SQL, Do you need to write a complex SELECT query? network profiles, well, writing complex SQL wouldn't be the only thing required to do that. It will help you to build a complex query from simpler “blocks” but also, you'll test  Learn How to Use SQL Quickly and Effectively And Become a Data Analysis Expert in Days! Join Over 50 Million People Learning Online with Udemy. 30-Day Money-Back Guarantee!

I'd use row_number to get the latest user details per uid, join them to the last visit date per uid and filter on that:

SELECT name, u.uid, department, max_visit
FROM   (SELECT name,
               uid,
               department,
               ROW_NUMBER() OVER (PARTITION BY uid ORDER BY id DESC) AS rn
        FROM   user_table) u
JOIN   (SELECT   uid, MAX(last_visit_date) AS max_visit
        FROM     user_visit_stat
        GROUP BY uid
        HAVING   MONTHS_BETWEEN(sysdate, MAX(last_visit_date)) >= 24) us ON u.uid = us.uid
WHERE  rn = 1              

Learn SQL: How to Write a Complex SELECT Query, However, most forget that SQL isn't just about writing queries, which is just the the data that is stored in the database can help you to formulate a pattern that start making simple queries more complex than they need to be. Search for Sql Database. Browse & Discover Useful Results!

Try below:

select a.uid,a.name,b.department,a.id,lastvisit
from
(select uid,name,max(id) as id
from USER_TABLE group by uid,name)a
inner join USER_TABLE b on a.uid=b.uid
inner join
(select uid,max(LAST_VISIT_DATE) as lastvisit
group by uid having max(LAST_VISIT_DATE)< trunc(sysdate, 'yyyy') - interval '2' year)x
on a.id=x.id

SQL Tutorial: How To Write Better Querie, Write answer for one query and add one one extra link to tha For beginners who just want to know the overview of SQL- W3 Schools. Its content is very  I need some help with a complex SQL query. Here's my setup: I have two tables USER_TABLE and USER_VISIT_STAT. USER_TABLE have columns like ID,USER_NAME,UID,DEPARTMENT and USER_VISIT_STAT have columns like ID,USERID,LAST_VISIT_DATE. USERID field of USER_VISIT_STAT table holds the USER_TABLE.ID.

Something like

select uid,name,department,last_visit_date
from (
  select 
    a.id id, max(a.id) over (partition by a.uid) maxid,
    a.uid uid,a.name name,a.department department,
    max(last_visit_date) over (partition by a.uid) last_visit_date
  from 
    user_table a, user_visit_stat b
  where 
    a.id=b.uid
)
where last_visit_date<=add_years(sysdate,-2) and a.id=maxid

What is the best way to learn complex SQL queries?, I want to actually understand the code I'm writing. More technical details: The database is hosted on a PostgreSQL server running on the local  Following are some Business Steps of Writing SQL Queries : 1.Analyse the Business Logic : The first step is to analyse the Business Logic. 2.Fragment the Business Logic : The second step is user needs to Fragment the Business logic and accordingly need to fragment logic.

use aggregate function and join

  select u.*,t.last_visit from     
    (
   select min(ID) as ID ,Name, UPPER(UID) as UID,DEPARTMENT from USER_TABLE
    group by Name, UPPER(UID),DEPARTMENT
    ) as u 
    join
    (
    select UID,max(LAST_VISIT_DATE) as last_visit from USER_VISIT_STAT us
    group by UID 
    having max(LAST_VISIT_DATE) between  add_months(sysdate,-26) and add_months(sysdate,-24)
    ) as t
    on u.ID=t.UID

From conversation it seems to me you need below

select u.*, x.last_visit from
       (
       select Name, UPPER(UID) as UID,DEPARTMENT from USER_TABLE
        group by Name, UPPER(UID),DEPARTMENT
        ) as u 

        join
        (   
        select a.name,a.last_visit from
        (
        select distinct iu.NAME,last_visit from
        (
        select USID,max(LAST_VISIT_DATE) as last_visit from USER_VISIT_STAT us
        group by USID 
        having max(LAST_VISIT_DATE) between  add_months(sysdate,-26) and add_months(sysdate,-24)
        ) as t1 
        join
        USER_TABLE as iu on t1.USID=iu.ID
        ) a left join           

        (
        select distinct iu.NAME,    
        (
        select USID  from USER_VISIT_STAT us
        group by USID 
        having min(LAST_VISIT_DATE) between  add_months(sysdate,-23) and add_months(sysdate,0)
        ) as t1 
        join
        USER_TABLE as iu on t1.UID=iu.ID    
        ) b on a.NAME=b.NAME
        where b.NAME is null 
        ) x on u.Name=x.Name

How do I make complex SQL queries easier to write?, SELECT lpad ('*', ROWNUM,'*') FROM Student WHERE ROWNUM <4;. 27.How to add the email validation using only one query? Answer : User needs to use  Need Help in writing complex sql query. My desired result is, 1.)If RegionId and DistrictId exists then get the Transplant days of this combination else get the Transplant days for RegionId and DistrictId(NULL).

Complex SQL Queries, Firstly, though, ask yourself whether you need a long, complex query at all. Hence the crucial piece of advice is to check whether or not the large query Robert de Graaf has written a number of pieces on SQL for Data  Need help writing a complex SQL query. Matt Cassarino. September 28, 2009 11:12AM Re: Need help writing a complex SQL query. Meg Liebenstein.

Complex Queries in SQL, Let's go through it step by step: Before creating our final Select statement, let's see which tables require LEFT joins and which do not. We need  Enforcing the same style over queries is important too. For example if you are writing queries for Microsoft SQL Server and you decided to use [TableName] instead of TableName, stick with it. If you go to a new line after a select, don't do it in only half of your queries, but all of them.

Example of complex SQL query to get as much data as possible , Explain Complex SQL Queries with Examples - list of Complex SQL Queries. You need to work on the testing logic and separate the level of nesting too. Read the code carefully that will help you to find errors certainly. Learn SQL by doing 50+ interactive coding exercises. Start now!

Comments
  • have you already tried something? Or where exactly do you fail?
  • Yes, i am able to get uniq UID's which are not connected since past 2 years. but when i am trying to get there max(VISIT_DATE) proper result is not comming.
  • Show us your current query attempt.
  • in the text you mention LAST_ACTIVE_DATE, but I think this should be LAST_VISIT_DATE. If so then please change it.
  • Why do you use the caption MAX(LAST_VISIT_DATE)? Isn't this the column LAST_VISIT_DATE?
  • I am getting proper result in fiddle. I am using oracle sql developer. So there its not working
  • @Prakashkumarmallick though you said not working but you accepted this answer
  • Its working fine now. So i accepted. But thanks for your help @Zaynul. I can't accept two answer otherwise i will accept your's also.This query is much simpler than yours so i accepted this. Anyway Thanks a lot Zaynul.
  • @ZaynulAbadinTuhin to inform you your query is also giving the proper result.
  • I think you might be missing a right parenthesis closing the nested SELECT in your join.