Is there any alternate way to simply the queries

user_18486356 0
Viewed 42

Is there any way to combine 2 SQL queries into a single query?

The table names used in both the SQL queries are same. The condition in the WHERE clause changes in both the queries.

Query 1:

SELECT ID
       ,SUM(time_duration) AS total_timespent
       ,COUNT(pno) AS ts_cnt
FROM (SELECT LEAD(create_ts) OVER (PARTITION BY ID ORDER BY CAST(pno AS INT)) AS R1,
DATEDIFF('second',create_ts::timestamp ,LEAD::timestamp) AS time_dur
FROM table_name
)A
WHERE item_no = '5672'
AND page_url_v11 IS NOT NULL
GROUP BY ID

Query 2:

SELECT ID
      ,SUM(time_duration) AS search_timespent
      ,COUNT(pno) AS search_cnt
from (SELECT LEAD(create_ts) OVER (PARTITION BY ID ORDER BY CAST(pno AS INT)) AS R1,
DATEDIFF('second',create_ts::timestamp ,LEAD::timestamp) AS time_dur
FROM table_name

)A
WHERE item_no = '5646'
GROUP BY ID
snowflake-cloud-data-platform
3 Answers

You could use Union operation to join statements together.

user_191558530

If you want duplicates use UNION ALL ,

If you don't want duplicates use UNION

user_191204490

Yes, it is possible to combine both of them using conditional aggregation:

WITH A AS (
    SELECT *
      ,LEAD(create_ts) OVER (PARTITION BY ID ORDER BY CAST(pno AS INT)) AS R1
      ,DATEDIFF('second',create_ts::timestamp ,LEAD::timestamp) AS time_duration
    FROM table_name
)
SELECT 
    ID
   ,SUM(CASE WHEN item_no = '5672' AND page_url_v11 IS NOT NULL 
             THEN time_duration END) AS total_timespent
   ,COUNT_IF(item_no = '5672' AND page_url_v11 IS NOT NULL) AS ts_cnt

   ,SUM(CASE WHEN item_no = '5646' THEN time_duration END) AS search_timespent
   ,COUNT_IF(item_no = '5646') AS search_cnt
FROM A
GROUP BY ID;
user_50708790
Related