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?
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'