Skip to content

Instantly share code, notes, and snippets.

@adamfortuno

adamfortuno/original.sql

Last active April 15, 2021 00:57
Show Gist options
  • Save adamfortuno/8a1d3fa1d76d12a428beb446cc5abe9f to your computer and use it in GitHub Desktop.
Save adamfortuno/8a1d3fa1d76d12a428beb446cc5abe9f to your computer and use it in GitHub Desktop.
Download ZIP
Provide a list of products that do not have data for everyday. You may pick a single site to focus on.
Raw
original.sql
--Original query...
SELECT CID,
min_price,
collection_date,
FROM QL2_TMP.PUBLIC.7PLUS_OVERLAP_SAMPLE_DATA_VIEW
WHERE site_name = 'Walmart'
AND year(date(Collection_date, 'dd-mm-yyyy')) = '2020'
AND CID in (4548496, 4548521, 4548615, 4548664, 4548672, 4548681, 4548684, 4548688, 4548732, 4548723)
ORDER BY collection_date DESC;
Raw
revised.sql
--Revised query...
WITH dates AS (
SELECT TRUNC(SYSDATE - 365 - ROWNUM) AS reporting_date
FROM DUAL CONNECT BY ROWNUM < 366
)
SELECT sdat.CID,
FROM dates dts LEFT JOIN "QL2_TMP"."PUBLIC"."7PLUS_OVERLAP_SAMPLE_DATA_VIEW" sdat
ON TO_CHAR(dts.reporting_date, 'YYYY-MM-DD') = TO_CHAR(sdat.Collection_date, 'YYYY-MM-DD')
WHERE sdat.site_name = 'Walmart'
AND sdat.Collection_date IS NULL
GROUP BY sdat.CID
ORDER BY sdat.CID;
@adamfortuno
Copy link
Author

adamfortuno commented Apr 15, 2021 •
edited

This looks like Oracle SQL? That's what I'm going with.

The first bit...

WITH dates AS (...)

Creates a temp table with all days from April 14, 2020 thru today. The next bit...

SELECT sdat.CID,....

Retrieves all products that don't have a collection_date that falls in one of those dates (April 14, 2020 thru today). A couple of things...

  • I assume "CID" is a unique identifier for the product?
  • I made the sample period each day from 1-year ago today. If that isn't the case, you can adjust the sample period through that first query.
  • I don't have an Oracle instance in front of me to test this query out. You might have to fix a syntax error or two.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment