Monthly Transactions Ii

Table: Transactions +----------------+---------+ | Column Name | Type | +----------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +----------------+---------+ id is the primary key of this table. The table has information about incoming transactions. The state column is an enum of type ["approved", "declined"]. Table: Chargebacks +----------------+---------+ | Column Name | Type | +----------------+---------+ | trans_id | int | | charge_date | date | +----------------+---------+ Chargebacks contains basic information regarding incoming chargebacks from some transactions placed in Transactions table.

by lek tin in "database" access_time 2-min read

Monthly Transactions I

Table: Transactions +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | country | varchar | | state | enum | | amount | int | | trans_date | date | +---------------+---------+ id is the primary key of this table. The table has information about incoming transactions. The state column is an enum of type ["approved", "declined"]. Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.

by lek tin in "database" access_time 2-min read

Reported Posts II

Table: Actions +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | post_id | int | | action_date | date | | action | enum | | extra | varchar | +---------------+---------+ There is no primary key for this table, it may have duplicate rows. The action column is an ENUM type of ('view', 'like', 'reaction', 'comment', 'report', 'share'). The extra column has optional information about the action such as a reason for report or a type of reaction.

by lek tin in "database" access_time 3-min read

Reported Posts

Table: Actions +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | post_id | int | | action_date | date | | action | enum | | extra | varchar | +---------------+---------+ There is no primary key for this table, it may have duplicate rows. The action column is an ENUM type of ('view', 'like', 'reaction', 'comment', 'report', 'share'). The extra column has optional information about the action such as a reason for report or a type of reaction.

by lek tin in "database" access_time 2-min read

Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null. +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+ Solution CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE M INT; SET M = N - 1; RETURN ( SELECT IFNULL( ( SELECT DISTINCT Salary FROM Employee ORDER BY Salary Desc LIMIT 1 OFFSET M ), NULL ) ); END

by lek tin in "database" access_time 1-min read

Reformat Department Table

Table: Department +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ (id, month) is the primary key of this table. The table has information about the revenue of each department per month. The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]. Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.

by lek tin in "database" access_time 2-min read

Big Countries

There is a table World +-----------------+------------+------------+--------------+---------------+ | name | continent | area | population | gdp | +-----------------+------------+------------+--------------+---------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000 | | Albania | Europe | 28748 | 2831741 | 12960000 | | Algeria | Africa | 2381741 | 37100000 | 188681000 | | Andorra | Europe | 468 | 78115 | 3712000 | | Angola | Africa | 1246700 | 20609294 | 100990000 | +-----------------+------------+------------+--------------+---------------+ A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

by lek tin in "database" access_time 1-min read

Report Contiguous Dates

Table: Failed +--------------+---------+ | Column Name | Type | +--------------+---------+ | fail_date | date | +--------------+---------+ Primary key for this table is fail_date. Failed table contains the days of failed tasks. Table: Succeeded +--------------+---------+ | Column Name | Type | +--------------+---------+ | success_date | date | +--------------+---------+ Primary key for this table is success_date. Succeeded table contains the days of succeeded tasks. A system is running one task every day. Every task is independent of the previous tasks.

by lek tin in "database" access_time 3-min read

Human Traffic of Stadium

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_date, people Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive). For example, the table stadium: +------+------------+-----------+ | id | visit_date | people | +------+------------+-----------+ | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-08 | 188 | +------+------------+-----------+ For the sample data above, the output is:

by lek tin in "database" access_time 2-min read

Consecutive Available Seats

Several friends at a cinema ticket office would like to reserve consecutive available seats. Can you help to query all the consecutive available seats order by the seat_id using the following cinema table? | seat_id | free | |---------|------| | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | Your query should return the following result for the sample case above.

by lek tin in "database" access_time 1-min read