Sales Queries

Querying Sales Raw & Sales Standardized

-- ==========================================================================
-- QUERYING SALES_RAW--
-- ==========================================================================
--QUERYING SALES_RAW--
SELECT source_document, sales_date, *
FROM sales.sales_raw
WHERE property_id in (${PROP IDS SEP BY COMMAS})
AND (to_date(sales_date :: TEXT, 'MM/DD/YYYY') >= '${MINDATE}'
      AND to_date(sales_date :: TEXT, 'MM/DD/YYYY') < '${MAXDATE LESS THAN}')
 ORDER BY to_date(sales_date :: TEXT, 'MM/DD/YYYY') DESC;

--QUERYING SALES_RAW by source_document
SELECT source_document, created_ts, property_id, * FROM sales.sales_raw WHERE source_document = '${SOURCEDOC}';
-- OR --
SELECT source_document, created_ts, property_id, * FROM sales.sales_raw WHERE property_id=${PROPID} and source_document = '${SOURCEDOC}';


-- FIND SCRAPER (WALL-E) SALES --
select source_document, count(*) from sales.sales_raw where
property_id in (${PROPIDS SEP BY COMMAS})
and source_document like '%Wall-E%'
group by source_document;


-- ==========================================================================
-- QUERYING SALES_STANDARDIZED--
-- ==========================================================================
SELECT * FROM sales.sales_standardized
WHERE property_id in (${PROP IDS SEP BY COMMAS})
and sales_date >= '${MINDATE}'
and sales_date <  '${MAXDATE LESS THAN}';


-- QUERYING SALES_STANDARDIZED WITH SALES_RAW sale_type, original new_or_used, total_gross--
SELECT sales.sales_standardized.*, sales.sales_raw.sale_type, sales.sales_raw.new_or_used, sales.sales_raw.total_gross
FROM sales.sales_standardized JOIN sales.sales_raw on sales_standardized.raw_sale_id = sales_raw.raw_sale_id
WHERE sales_standardized.property_id in (${PROP IDS SEP BY COMMAS})
and sales_standardized.sales_date >= '${MINDATE}'
and sales_standardized.sales_date <  '${MAXDATE LESS THAN}';

-- QUERYING SALES_STANDARDIZED: FINDING ALL SOURCE DOCS FOR A PROPERTY --
SELECT source_document, count(*) FROM sales.sales_standardized
where property_id in (${PROPIDS Separated by Commas})
and sales_date >= '${MINDATE}'
GROUP BY source_document;

--QUERYING SALES_STANDARDIZED by source_document
SELECT source_document, * FROM sales.sales_standardized WHERE source_document like '${SOURCEDOC}%';
-- OR --
SELECT source_document, * FROM sales.sales_standardized WHERE property_id=${PROPID} and source_document like '${SOURCEDOC}%';




-- Sales Standardized Counts by Month
SELECT to_date(sales_date:: TEXT, 'YYYY-MM'), count(*)
FROM sales.sales_standardized
WHERE property_id in (${PROP IDS SEP BY COMMAS})
AND sales_date >= '${MINDATE}'
AND sales_date < '${MAXDATE LESS THAN}'
GROUP BY to_date(sales_date:: TEXT, 'YYYY-MM')
ORDER BY to_date(sales_date:: TEXT, 'YYYY-MM') ASC;


-- Sales Standardized GROSS SUMMARY
-- Note: Total Gross does not exist in Sales Standardized, so we are pulling it in from Sales Raw using a Join
SELECT sales_raw.property_id, to_date(sales.sales_standardized.sales_date:: TEXT, 'YYYY-MM') as "sales month", sum(sales.sales_standardized.front_gross :: DEC) as "front_gross", sum(sales.sales_standardized.back_gross:: DEC) as "back_gross", sum(sales.sales_raw.total_gross:: DEC) as "total_gross",  (sum(sales.sales_standardized.front_gross:: DEC) + sum(sales.sales_standardized.back_gross:: DEC)) as "total gross calculated", sum(sales.sales_standardized.price :: DEC) as "price"
FROM sales.sales_standardized JOIN sales.sales_raw on sales_standardized.raw_sale_id = sales_raw.raw_sale_id
JOIN mongodb.prod_properties_ft on mongodb.prod_properties_ft.property_id = sales_raw.property_id
WHERE sales_standardized.property_id in (${PROP IDS SEP BY COMMAS})
and sales_standardized.sales_date >= '${MINDATE}'
and sales_standardized.sales_date <  '${MAXDATE LESS THAN}'
GROUP BY sales_raw.property_id, to_date(sales.sales_standardized.sales_date:: TEXT, 'YYYY-MM')
ORDER BY sales_raw.property_id, to_date(sales.sales_standardized.sales_date:: TEXT, 'YYYY-MM') ASC;

Querying Sales Filtered

--QUERYING SALES_FILTERED BY PROPERTY--
SELECT r.property_id, r.sales_date, * FROM sales.sales_filtered f
    JOIN sales.sales_raw r ON f.raw_sale_id = r.raw_sale_id
WHERE property_id in (${PROPIDS SEP BY COMMAS})
and filtered_date >= '${MIN FILTERED DATE}'
ORDER BY filtered_date DESC;

--QUERYING SALES_FILTERED FOR PROPERTY NOT ACTIVE... BY PROPERTY WITH SA ACTIVE DATES--
SELECT r.property_id, r.sales_date, p.active_dates, r.source_document, * FROM sales.sales_filtered f
    JOIN sales.sales_raw r ON f.raw_sale_id = r.raw_sale_id
    JOIN claripedia.property_active_dates p ON r.property_id = p.property_id
WHERE r.property_id in (${PROPIDS SEP BY COMMAS})
and filtered_date >= '${MIN FILTERED DATE}'
and p.clarivoy_product='SA'
and f.reason = 'Property Not Active for This Date'
ORDER BY r.property_id ASC;

--QUERYING SALES_FILTERED BY SOURCE DOCUMENT--
SELECT * FROM sales.sales_filtered f
    JOIN sales.sales_raw r ON f.raw_sale_id = r.raw_sale_id
WHERE r.source_document like '${SOURCEDOC}%'
and filtered_date >= '${MIN FILTERED DATE}'
ORDER BY filtered_date DESC;

--FILTERED COUNTS WITHIN THE PAST 1 DAY--
SELECT reason, count(*) FROM sales.sales_filtered
where filtered_date >= (now() - interval '1 DAY')
GROUP BY reason ORDER BY reason ASC;

Sales Statements

Deleting A Sales File

-- FINDING A PROCESSED SALES FILE --
-- 1. Find the name of the file - either in Leads Preprocessing board Source Document column
--    OR use this query.  Note: Filename may not always be what you think it is (for Excel files, it often appends _Sheetname)
SELECT * FROM sales.sales_raw WHERE property_id in (${PROPID});
-- OR --
SELECT source_document, count(*) from sales.sales_raw where property_id in (${PROPID}) group by source_document;
-- OR --
SELECT sales_date, count(*) from sales.sales_raw where property_id in (${PROPID}) group by sales_date order by sales_date desc;



-- DELETING A PROCESSED SALES FILE --
-- 2. Delete the file --
-- This will also remove the data from leads.filtered and leads.standardized as well --
-- Do NOT set reprocess_row=TRUE also!! The DAG will pick up the rows and delete them with only delete_row=TRUE.
UPDATE sales.sales_raw SET delete_row = TRUE WHERE property_id = '${PROPID}' and source_document like '${SOURCEDOC}%';
-- OR WITHOUT PROPID (FOR MULTI FILES)
UPDATE sales.sales_raw SET delete_row = TRUE WHERE source_document like '${SOURCEDOC}%';
-- 3. Run etl_sales_preprocessing_processing to delete the rows out!!
-- 4. Don't forget to delete the file out of Data Archive!

Deleting All Sales For a Property

-- DELETING ALL SALES FOR A PROPERTY - *** USE WITH CAUTION *** --
--1. First double check what you are going to delete--
SELECT * from sales.sales_raw where property_id= '${PROPID}';
-- OR --
SELECT source_document, count(*) from sales.sales_raw where property_id in (${PROPID}) group by source_document;
-- OR --
SELECT sales_date, count(*) from sales.sales_raw where property_id in (${PROPID}) group by sales_date order by sales_date desc;
--2. Then delete it
UPDATE sales.sales_raw SET delete_row = TRUE WHERE property_id = '${PROPID}';
-- 3. Run etl_sales_preprocessing_processing to delete the rows out!!



-- DELETING ALL SALES FOR A PROPERTY BY DATE --
--1. First double check what you are going to delete--
SELECT * from sales.sales_raw WHERE property_id = '${PROPID}'
AND (sales_date >= '${STARTDATEYYYY-MM-DD}'
     AND sales_date < '${ENDDATE_LESSTHAN_YYYY-MM-DD}')
ORDER BY sales_date  DESC;
-- OR if the dates are in M/D/YYYY format, use this instead
SELECT * from sales.sales_raw WHERE property_id = '${PROPID}'
AND (to_date(sales_date :: TEXT, 'MM/DD/YYYY') >= '${STARTDATEYYYY-MM-DD}'
      AND to_date(sales_date :: TEXT, 'MM/DD/YYYY') < '${ENDDATE_LESSTHAN_YYYY-MM-DD}')
 ORDER BY to_date(sales_date :: TEXT, 'MM/DD/YYYY') DESC;

--2. Then delete it
UPDATE sales.sales_raw SET delete_row = TRUE WHERE property_id = '${PROPID}'
AND (sales_date >= '${STARTDATEYYYY-MM-DD}'
     AND sales_date < '${ENDDATE_LESSTHAN_YYYY-MM-DD}');
-- OR if the dates are in M/D/YYYY format, use this instead
UPDATE sales.sales_raw SET delete_row = TRUE WHERE property_id = '${PROPID}'
AND (to_date(sales_date :: TEXT, 'MM/DD/YYYY') >= '${STARTDATEYYYY-MM-DD}'
      AND to_date(sales_date :: TEXT, 'MM/DD/YYYY') < '${ENDDATE_LESSTHAN_YYYY-MM-DD}');
-- 3. Run etl_sales_preprocessing_processing to delete the rows out!!

Manually Deleting Sales from All Tables

-- =========================================================================================================
-- MANUALLY DELETING sales from all tables (only use when there is a table conflict causing ETL to fail!)
delete from sales.sales_filtered where raw_sale_id='{SALEID}}';
delete from sales.sales_latlon_attribution where sale_id='{SALEID}';
delete from sales.matched_cdc where sale_id='{SALEID}';
delete from sales.sales_extract where sale_id='{SALEID}';
delete from sales.sales_standardized where raw_sale_id='{SALEID}';
delete from sales.sales_raw where raw_sale_id='{SALEID}';

select property_id, * from sales.sales_raw where raw_sale_id='a04cb255-94f9-4f3f-9beb-8f25299677a0';
select property_id, * from sales.sales_raw where raw_sale_id='f3556d1b-1b00-4f51-b492-5e598e8b6b11';

Deleting CDK API Sales

To delete out CDK sales, you must delete from 2 tables:

• sales.sales_raw - delete by setting delete_row=TRUE
• sales.cdk_raw - delete by using a DELETE statement. BE CAREFUL!!! Get approval from Engineering prior to executing this.

-- DELETING ALL SALES FOR A PROPERTY IN CDK RAW - *** USE WITH CAUTION *** --
--1. First double check what you are going to delete from CDK RAW--
SELECT * from sales.cdk_raw where property_id= '${PROPID}';
-- OR --
SELECT source_document, count(*) from sales.cdk_raw where property_id in (${PROPID}) group by source_document;
-- OR --
SELECT sales_date, count(*) from sales.cdk_raw where property_id in (${PROPID}) group by sales_date order by sales_date desc;

--2. Then delete it from CDK RAW
DELETE FROM sales.cdk_raw WHERE property_id = '${PROPID}';
-- Nothing additional required to delete from cdk_raw.  --
-- But make sure you set delete_row=true in sales_raw so sales are deleted in raw and standardized! --

Sales loaded to Wrong Property ID

-- =============================================================================
-- FIXING PROPERTY ID FOR SALES
-- =============================================================================
-- Fixing the Property ID FOR ALL SALES ON A PROPERTY UNDER THE SAME CLIENT
-- 1. Move the data -
update sales.sales_raw set property_id = ${NEWPROPID}, reprocess_file=true
where property_id = ${OLDPROPID};
-- 2. If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- 3. If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.


-- Fixing the Property ID FOR ALL SALES ON A PROPERTY UNDER A DIFFERENT CLIENT
-- Remember you must update Client ID also (if it changed)!
-- 1. Move the data -
update sales.sales_raw set property_id = ${NEWPROPID}, client_id = ${NEWCLIENTID}, reprocess_file=true
where property_id = ${OLDPROPID};
-- 2. If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- 3. If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.

--FIXING PROPERTY ID AND SOURCE DOCUMENT BOTH
-- Remember you must update Client ID and Sales Standardized source_document.
-- 1. Select both statements and run
UPDATE sales.sales_standardized SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}' and property_id = '${PROPID}';
UPDATE sales.sales_raw set property_id = ${NEWPROPID}, client_id = ${NEWCLIENTID}, source_document='${NEWDOCNAME}', reprocess_file=true where property_id = ${OLDPROPID} and source_document='${OLDDOCNAME}';
-- 2. Rename the file in Data Archive and/or SFTP!
-- 3. If any Collects exist, make sure you rename all Dropbox IDD & Data Archive folders to the correct Property ID!
-- 4. If any Collects, Downloads, Automated Reports make sure you fix the property aliases in the property_aliases table.

Fixing a Sales Source Document Name

-- FIXING SOURCE_DOCUMENT FOR SALES FILES --
-- If you loaded a file and named it incorrectly (for example put the wrong year in the filename)
-- you can simply correct the source_document by updating sales_raw and sales_standardized
-- The file does not need to be reprocessed.
-- Remember to rename the file in Data Archive after you are done here or it could cause a conflict in the future!!

-- Updating by source_document and property_id (for single property files)
-- 1. Select both statements and run
UPDATE sales.sales_raw SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}' and property_id = '${PROPID}';
UPDATE sales.sales_standardized SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}' and property_id = '${PROPID}';
-- 2. Rename the file in Data Archive and/or SFTP!

-- Updating by source_document only (for multi files)
-- 1. Select both statements and run
UPDATE sales.sales_raw SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}';
UPDATE sales.sales_standardized SET source_document='${NEWDOCNAME}' where source_document='${OLDDOCNAME}';
-- 2. Rename the file in Data Archive and/or SFTP!