Blizzard Oracle Developer Paper Exam

 
언젠가 우연하게 받아 봤던 Blizzard 의 Oracle Developer Position 의 1st Paper Exam 문제 이다.
그 때 느낀 건데 내가 너무 자만 하고 준비가 안되어 있구나 했다. 

  One Way Ticket
  Digital Nomad

Battle.net Database Developer Take Home Test #1

The “Awesome Game Show” will be hosting a limited number of tickets per seating tier:
 Tier R 100 seats
 Tier S 500 seats
 Tier A 1000 seats
 Tier B 2000 seats

It is extremely important to maximize concurrency for the ticket sales due to popularity of the “Awesome
Game Show” which can sell out in minutes with an extremely high number of concurrent transactions.
Under the two different scenarios listed below, implement functions/procedures to reserve and cancel reservation using PL/SQL package(s).

Case 1. All seats are pre-assigned to a specific seat number. 

 Tier R Seat Numbers R1-R100
 Tier S S1-S500
 Tier A A1-A1000
 Tier B B1-B2000

Case 2. All seats in a specific tier can be openly assigned. In this case, cancelled reservations do not become available for re-sale. Once tickets are sold out, ticket sales are finished and only cancellations can be made

[Assignments]

1. Use a preferred modeling tool to draw out a logical model and submit a screen captured image file of the model.

2. Submit a screen captured image file of a physical model and table/index creation scripts for Oracle DBMS.

3. Submit Oracle PL/SQL package source file(s) for the following functions: reservation and reservation cancellation.

Battle.net Database Developer Take Home Test #2

“B” Corp. wants to build a virtual currency system and use it as their payment method to purchase their products/services. Requirements for this system in charging up and spending the virtual money are listed as follows.

- Charging up virtual currency.

1. The virtual currency can be charged up using real life currency (Real money credit) using cash, credit/debit card, and other real money payment methods. Virtual money can also be charged up without using real money (Non-real money credit) through compensations and events.

2. Non-real money credits expire in 5 years. Expiration is set to 00:00 on a day of 5 years from the charge up. Expired non-real money credits are unusable and the total balance should reflect this rule.

3. When charging up real money credit maximum balance is limited by a daily, weekly, and monthly threshold with respect to charge up time.

- Spending virtual money.

1. Purchasing products/services must use non-real money credits before using real money credits. However, there is no priority on using different types (compensation vs events) of non-real money credit but credits should be consumed in the order that they were added from oldest to newest.

For example, a user was granted $10 of non-real money credit on January 1, 2014 and then granted another $10 of non-real money credit for a different event on January 15, 2014. When this user initiates a purchase of an in-game character name change, the ending balance should be $5 and this balance must expire on January 5, 2019 00:00:00. You must be able to show that this remaining $5 is a residual amount of credit granted by an event on January 15, 2014 in the amount of $10.

The virtual money system must be rapid and robust since “B” Corp services millions of customers and all purchases from the company uses this system. Implementation of database design and PL/SQL must consider high throughput of B’s needs. Consistency is a must since the virtual currency system is a finance related service.

[Assignments]

1. Use a preferred modeling tool to draw out a logical model and submit a screen captured image file of the model.

2. Submit a screen captured image file of a physical model and table/index creation scripts for Oracle DBMS. The physical model must consider high performance read/write operations even with massive
amount of data.

3. Submit Oracle PL/SQL package source file(s) for the following functions:

o Credit charge up

o Consume credits

o Expose a list of each user’s charge up/consume activities. Search date range will be provided by API caller. Design this API to expose data set to a web server. Web display will use pagination. Therefore, your API should only return matching data set for requested page number. Each page needs to display 10 records.

o Expose each user’s total balance, total real money balance, and total non-real money balance. Non-real money balance must exclude expired credits.

o Take a date range of a minimum of one month of time and expose daily sum of charge up/consume credit amount, 7 day moving average of charge up/consume amount, and delta in daily charge up/consume amount compared to the previous day. The result set should show data for every day and display $0 for the days that no activity is detected but the result set must display data for every day. (Do not use cursor for this API.)