interview question in a SQL development context URGENET HELP NEEDED

归档 职场创业理财
#1

:cn17:
I cant type chinese atm, but since this thread is hot, can someone have a look at this question and help me out for an interview tmr please

The company publishes a variety of (presently) disparate but inter-related data sets held in separate silos; we need them integrating within a relational database

How would you go about structuring our data output?

there’s an excel sheet with bond datas columns in

Cusip Isins Name Deal type Price Type Price Size Delete DeleteDate

Attached is a day’s worth of bwic data sent to clients; each line of the trade data relates to a slightly different risk profile within a bond. Any one bond (identified by Name column) is sliced into various ‘tranches’ depending on the risk profile of the collateral in the bond. So for a RMBS (pool of residential mortgage loans) the risk profiles will start at low risk (e.g. doctors’ & lawyers’ mortgage loans) to relatively high risk (e.g. part time/temporary workers.) Each ‘tranche’ is represented by a unique cusip or isin.

I am accouting student, no idea how they’d found my cv to be fitting, it’s an internship but I need it so bad for money, and more so because my last work experience was too long ago.

it shouldn’t be very complicated, as the spec asks for degree not in CS/ENGINEERING but business, finance etc…

i’ve search on the web, and found some youtube videos related, but still cant seem to get it.

can someone throw me some links , for a quick guide? I’d be so grateful

thanks alot

#2

Where is the raw data?

#3

i’ve emailed you, thank you
please take a look

#4

From what you said, i would create two tables. A staging table to simply extract & load the raw data from the excel file through a SSIS package and a source table (also called the destination table) to store the clean trade data in the risk order. In this case, a stored proc to process the raw data from the staging table to the source/destination table is also required. It’s like doing a massage to transform the data from raw to the info that you need in relational db.

You can also combine the two tables (or the two stages) as one table/ one process, and processing data from staging table to source table can be integrated into the ETL process. However it depends on the volume of the data processing daily. It’s hard to suggest more without seeing the raw data and knowing more about business requirements. At the moment all I can tell is that when you create the table, ‘Cusip’ and ‘Isins’ columns need to have UNIQUE constrains.

Is the ‘pool of residential mortgage loans’ a table existing on the relational database?