CODEWITHSUNDEEP
snowflake-cloud-data-platform
Sherin Shaziya
Sherin Shaziya

Reputation: 147

Is there any alternate way to simply the queries

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

Upvotes: 1

Views: 68

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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;

Upvotes: 1

DontDownvote
DontDownvote

Reputation: 192

If you want duplicates use UNION ALL ,

If you don't want duplicates use UNION

Upvotes: 1

BugFreeDuck
BugFreeDuck

Reputation: 191

You could use Union operation to join statements together.

Upvotes: 1

Related Questions