SQL Mentor Needed for Job Advancement: SQL Code Review Request

Code Feedback
1

Hi there,

I have been a Data Analyst for 3 years and I’m recently trying to get a new job. I got an interview at an University and they sent me a technical survey. Tests make me incredibly nervous and I second guess my own abilities.

My hope is to get someone to review my answers with me for the best chances with the job.
The test was given on a word docx and I can’t upload it to here, so here are some screen shots. I also cannot run any of my queries to check my work.

Any takers wanna help me change my life?

image
image1131×1054 127 KB

Q1 Answer:

-- Description: Show number of days prospect has been in current status
-- As P.PROSPECTSTATUSCODEID = PSCA.AUDITRECORDID then the field indicates the current status of the ID in the table PROSPECT. I pulled the ID of the Prospect, the description from the 2nd table and then used datediff to get a whole number/number of days at current status as indicated in PROSPECT table.

SELECT
P.ID as Prospect_ID,
PSCA.DESCRPTION as Current_Status,
DATEDIFF( day , getdate(), P.DATECHANGED) as Days_at_Current_Status
from PROSPECT P
join PROSPECTSTATUSCODEAUDIT PSCA on P.PROSPECTSTATUSCODEID = PSCA.AUDITRECORDID

Question 2: Alumni status is stored as a constituency code on a record. A constituency of ‘Current 
Student’ should be added to records with an active incomplete educational history associated with 
PSU, and constituency of ‘Degreed Alumni’ added when a status is updated to ‘Graduated’ on a 
record. Write a stored procedure that can be used to automate the updates to these constituency 
values. 

Assume the following table structures:  

CONSTITUENTCONSTITUENCY (
ID uniqueidentifier
	CONSTITUENTID uniqueidentifier
	CONSTITUENCYCODEID uniqueidentifier
	DATEFROM datetime
	DATETO datetime
	DATEADDED datetime
	DATECHANGED datetime
	)

EDUCATIONALHISTORY (
ID uniqueidentifier
	CONSTITUENTID uniqueidentifier
	EDUCATIONALINSTITUTIONID uniqueidentifier
	EDUCATIONALHISTORYTYPECODE uniqueidentifier
	EDUCATIONALHISTORYSTATUSCODE uniqueidentifier
CONSTITUENCYSTATUSCODE tinyint
CONSTITUENCYSTATUS nvarchar(19)
ISPRIMARYRECORD bit
	STARTDATE char(8)
	ENDDATE char(8)
	CLASSOF smallint
	DATEADDED datetime
	DATECHANGED datetime
	)

Q2: Answer

-- Create Date: 2019-08-02
-- Description: Procedure for updating Constituency Values
-- ****Disclaimer: This is the first procedure I have ever written. I have never been an analyst with 
permissions to update/set/create anything within a given database. I would need to practice/learn more 
about this to increase comfort.****

-- To find ‘active’ status, I would utilize > <= functions on a casted ENDDATE to determine if the record
 is active. Due to ENDATE being a char(8) field, to do date manipulation, I’d need to cast it to a data 
type that can interact with another date data type. Then set the status based off of either the casted 
ENDDATE value being null/incomplete, if possible, or a listed CLASSOF field with an ENDDATE prior 
to the current date. I guessed that the CLASSOF field may not populate until graduation data is added.


CREATE_PROCEDURE ConstValuesKE

AS
IF
	BEGIN
		UPDATE EDUCATIONALHISTORY
		SET CONSTITUENCYSTATUS = 'Current Student'
		WHERE cast(ENDDATE AS date) IS NULL OR >= current_date	
	END	
ELSE	
	BEGIN
		UPDATE EDUCATIONALHISTORY
		SET CONSTITUENCYSTATUS = 'Degreed Alumni'
		WHERE CONSTITUENCYSTATUS = 'Graduated' 
AND cast(ENDDATE AS date) <= current_date 
AND CLASSOF IS NOT NULL
	END
END
	
EXEC ConstValuesKE 


Question 3: The following query is suspected of returning duplicate records. What steps would you
 take to audit this report, and what changes would you make to ensure revenue is being report 
correctly? 

WITH tmpCampaignAmt as 
	(SELECT REVENUEID
		,VALUE
	FROM ATTRIBUTEE49DBAA53B4F416E8ECC48CA594417D9
	)

SELECT 
	RS.ID AS REVENUESPLITID,
	R.AMOUNT AS REVENUEAMOUNT,
	RS.AMOUNT AS REVENUESPLITAMOUNT,
	ISNULL(CMPA.VALUE,0) AS CAMPAIGNAMOUNT,
	CASE
		WHEN R.AMOUNT > 0
		THEN ROUND(ISNULL(CMPA.VALUE,0) * (RS.AMOUNT / R.AMOUNT),2)
		ELSE ISNULL(CMPA.VALUE,0)
	END
	AS CAMPAIGNSPLITAMOUNT,
	PG.NETPRESENTVALUE,
	CASE
		WHEN R.AMOUNT > 0
		THEN ROUND(ISNULL(PG.NETPRESENTVALUE,0) * (RS.AMOUNT / R.AMOUNT),2)
		ELSE ISNULL(PG.NETPRESENTVALUE,0)
	END
	AS NETPRESENTSPLITAMOUNT,
	CAST(A.VALUE AS DATE) AS CAMPAIGNDATE

FROM REVENUE R
INNER JOIN REVENUESPLIT RS ON R.ID = RS.REVENUEID
INNER JOIN PLANNEDGIFTREVENUE PR ON R.ID = PR.REVENUEID
INNER JOIN PLANNEDGIFT PG ON PR.ID = PG.ID
LEFT JOIN ATTRIBUTEE55C99181CD74C8BBA5B0B3E1204039C A ON PG.ID = A.PLANNEDGIFTID
LEFT JOIN tmpCampaignAmt CMPA ON R.ID = CMPA.REVENUEID

WHERE R.TRANSACTIONTYPE = 'Planned gift'

Q3: Answer

-- Create Date: 2019-08-02
-- Description: Remove Duplicate Records
-- Added DISTINCT to CTE & Main Query to remove duplicate values. If possible, find additional fields
 to join tables. To audit: check results against previous reports. Explore tables to familiarize myself with 
expected outcomes. 

WITH tmpCampaignAmt AS 
	(SELECT DISTINCT
		REVENUEID,
		VALUE
	FROM ATTRIBUTEE49DBAA53B4F416E8ECC48CA594417D9
	)

SELECT DISTINCT
	RS.ID AS REVENUESPLITID,
	R.AMOUNT AS REVENUEAMOUNT,
	RS.AMOUNT AS REVENUESPLITAMOUNT,
	ISNULL(CMPA.VALUE,0) AS CAMPAIGNAMOUNT,
	CASE
		WHEN R.AMOUNT > 0
		THEN ROUND(ISNULL(CMPA.VALUE,0) * (RS.AMOUNT / R.AMOUNT),2)
		ELSE ISNULL(CMPA.VALUE,0)
	END
	AS CAMPAIGNSPLITAMOUNT,
	PG.NETPRESENTVALUE,
	CASE
		WHEN R.AMOUNT > 0
		THEN ROUND(ISNULL(PG.NETPRESENTVALUE,0) * (RS.AMOUNT / R.AMOUNT),2)
		ELSE ISNULL(PG.NETPRESENTVALUE,0)
	END
	AS NETPRESENTSPLITAMOUNT,
	CAST(A.VALUE AS DATE) AS CAMPAIGNDATE

FROM REVENUE R
INNER JOIN REVENUESPLIT RS ON R.ID = RS.REVENUEID
INNER JOIN PLANNEDGIFTREVENUE PR ON R.ID = PR.REVENUEID
INNER JOIN PLANNEDGIFT PG ON PR.ID = PG.ID
LEFT JOIN ATTRIBUTEE55C99181CD74C8BBA5B0B3E1204039C A ON PG.ID = A.PLANNEDGIFTID
LEFT JOIN tmpCampaignAmt CMPA ON R.ID = CMPA.REVENUEID

WHERE R.TRANSACTIONTYPE = 'Planned gift'
1 Like
2

Sorry, I’d love to help, but I don’t know SQL or anyone that knows SQL.
try searching for the leaders of FCC.