getwd()## [1] "C:/Users/user/Desktop/Data analysis projects/Kickstarter/code"#Setting the working diectory and the connection to MYSQL Server
library(RMySQL)## Warning: package 'RMySQL' was built under R version 3.6.3## Loading required package: DBIsqlQuery <- function (query) {
# creating DB connection object with RMysql package
DB <- dbConnect(MySQL(), user='root', password = 'Ryerson2020', dbname = 'kickstarter', host = '127.0.0.1')
# send Query to obtain result set
rs <- dbSendQuery(DB, query)
# get elements from result sets and convert to dataframe
result <- fetch(rs, -1)
# close db connection
dbDisconnect(DB)
# return the dataframe
return(result)
}cons <- dbListConnections(MySQL())
for(con in cons)
dbDisconnect(con)First, we need to check how many tables in the database
sqlQuery("
SHOW tables;
")## Warning: Closing open result sets## Tables_in_kickstarter
## 1 campaign
## 2 category
## 3 country
## 4 currency
## 5 sub_categoryThere are fives tables, namely: campain, category, country, currency, and sub_category Check the content of individual table
sqlQuery("
SHOW COLUMNS
FROM campaign
")## Warning: Closing open result sets## Field Type Null Key Default Extra
## 1 id bigint NO PRI <NA>
## 2 name text YES <NA>
## 3 sub_category_id bigint YES MUL <NA>
## 4 country_id bigint YES MUL <NA>
## 5 currency_id bigint YES MUL <NA>
## 6 launched datetime YES <NA>
## 7 deadline datetime YES <NA>
## 8 goal double YES <NA>
## 9 pledged double YES <NA>
## 10 backers bigint YES <NA>
## 11 outcome text YES <NA>The description of each column as follow ID: unique project ID name: project name sub_category_id: what industry/category was the project in? country_id: id number of country of origin currency_id: currency funding was given in ? launched: date fundraising began deadline: when target amount must be raised by goal: desired amount of funding pledged: how much was promised (whether or not the goal was reached) backers: how many people contributed to the campaign? outcome: the status of project (successful/failed/…)?
sqlQuery("
SHOW COLUMNS
FROM category
")## Warning: Closing open result sets## Field Type Null Key Default Extra
## 1 id bigint NO PRI <NA>
## 2 name text YES <NA>There are only two columns : the id of category and the category name
sqlQuery("
SHOW COLUMNS
FROM country;
")## Warning: Closing open result sets## Field Type Null Key Default Extra
## 1 id bigint NO PRI <NA>
## 2 name text YES <NA>There are only two columns : the id of country and the country name
sqlQuery("
SHOW COLUMNS
FROM currency;
")## Warning: Closing open result sets## Field Type Null Key Default Extra
## 1 id bigint NO PRI <NA>
## 2 name text YES <NA>There are also only two columns : the id of currency and the currency name
Exploring each table in data base
How many records in the table campaign?
sqlQuery("
SELECT COUNT(*)
FROM campaign;
")## Warning: Closing open result sets## COUNT(*)
## 1 15000sqlQuery("
SELECT COUNT(id)
FROM campaign;
")## Warning: Closing open result sets## COUNT(id)
## 1 1500015000 id, because id is premary key => not replicate The table campaign contains 15000 rows
To display the first 15 rows
sqlQuery("
SELECT *
FROM campaign
LIMIT 15;
")## Warning: Closing open result sets## id name
## 1 1 Ragdolls
## 2 2 One To Ten
## 3 3 Future Gun - A short film about kids and advanced hardware
## 4 4 Fourth Wave Apparel—Vintage Fashion for the Modern Feminist
## 5 5 The Rich Lifestyle
## 6 6 Perils Galore!
## 7 7 In a Dark Dark House - Directed by Larry Moss
## 8 8 CHARLIEFOXTROTNY
## 9 9 Fat Englishmen - Beer and Sumo
## 10 10 bear elements old-fashioned shave and beard care for men
## 11 11 DigIndustrial Art
## 12 12 Expanding Farm to service the local Food Bank
## 13 13 Church Hive. Church hiring made easy!
## 14 14 The Visual Media Directory Project
## 15 15 Too Sweet - The Not So Serious Side to Diabetes
## sub_category_id country_id currency_id launched
## 1 23 2 2 2013-04-25 00:00:00
## 2 47 1 1 2015-11-16 00:00:00
## 3 24 2 2 2013-08-28 00:00:00
## 4 52 2 2 2014-07-22 00:00:00
## 5 9 2 2 2016-08-24 00:00:00
## 6 27 2 2 2012-09-25 00:00:00
## 7 20 2 2 2014-07-01 00:00:00
## 8 23 2 2 2014-05-21 00:00:00
## 9 54 2 2 2015-03-03 00:00:00
## 10 12 2 2 2016-04-14 00:00:00
## 11 97 2 2 2014-06-19 00:00:00
## 12 107 2 2 2016-06-13 00:00:00
## 13 40 2 2 2016-09-01 00:00:00
## 14 26 2 2 2012-03-07 00:00:00
## 15 10 2 2 2014-01-16 00:00:00
## deadline goal pledged backers outcome
## 1 2013-05-25 00:00:00 15000.00 20.00 3 failed
## 2 2015-12-16 00:00:00 223.68 413.81 23 successful
## 3 2013-09-27 00:00:00 5000.00 1497.00 28 failed
## 4 2014-08-21 00:00:00 6000.00 8795.00 218 successful
## 5 2016-09-28 00:00:00 2000000.00 2.00 2 failed
## 6 2012-11-01 00:00:00 2500.00 230.00 5 failed
## 7 2014-08-15 00:00:00 55000.00 9316.00 45 failed
## 8 2014-06-20 00:00:00 6000.00 161.00 4 failed
## 9 2015-04-02 00:00:00 50000.00 0.00 0 failed
## 10 2016-05-14 00:00:00 12000.00 306.00 11 failed
## 11 2014-07-09 00:00:00 30000.00 111.00 4 failed
## 12 2016-07-13 00:00:00 2500.00 405.00 5 failed
## 13 2016-10-01 00:00:00 16800.00 101.00 2 failed
## 14 2012-05-06 00:00:00 10000.00 2.00 2 failed
## 15 2014-02-15 00:00:00 2500.00 2525.00 6 successfulThis table contains country_id and currency_id, the country_id can be found in table country. the id of currency appears in the table campaign and the table country.
How many subcategory, country, and currency ?
sqlQuery("
SELECT
COUNT(DISTINCT sub_category_id) as subidnb,
COUNT(DISTINCT country_id) as countrynb,
COUNT(DISTINCT currency_id) as currencynb
FROM campaign;
")## Warning: Closing open result sets## subidnb countrynb currencynb
## 1 157 22 13launched date and deadline date (datetime)
sqlQuery("
SELECT launched AS launched_date,
deadline AS deadline
FROM campaign
ORDER BY launched_date DESC
LIMIT 10;
")## Warning: Closing open result sets## launched_date deadline
## 1 2018-01-02 00:00:00 2018-03-03 00:00:00
## 2 2018-01-01 00:00:00 2018-01-31 00:00:00
## 3 2018-01-01 00:00:00 2018-02-11 00:00:00
## 4 2018-01-01 00:00:00 2018-01-31 00:00:00
## 5 2017-12-30 00:00:00 2018-01-29 00:00:00
## 6 2017-12-29 00:00:00 2018-01-28 00:00:00
## 7 2017-12-29 00:00:00 2018-01-28 00:00:00
## 8 2017-12-28 00:00:00 2018-01-29 00:00:00
## 9 2017-12-27 00:00:00 2018-01-26 00:00:00
## 10 2017-12-27 00:00:00 2018-01-31 00:00:00There is data for 2017 and 2018
sqlQuery("
SELECT launched AS launched_date,
deadline AS deadline
FROM campaign
ORDER BY launched_date ASC
LIMIT 10;
")## Warning: Closing open result sets## launched_date deadline
## 1 2009-05-06 00:00:00 2009-08-04 00:00:00
## 2 2009-05-07 00:00:00 2009-07-02 00:00:00
## 3 2009-05-10 00:00:00 2009-06-16 00:00:00
## 4 2009-05-12 00:00:00 2009-05-22 00:00:00
## 5 2009-06-04 00:00:00 2009-07-16 00:00:00
## 6 2009-06-05 00:00:00 2009-09-02 00:00:00
## 7 2009-06-20 00:00:00 2009-07-01 00:00:00
## 8 2009-06-30 00:00:00 2009-07-16 00:00:00
## 9 2009-07-14 00:00:00 2009-09-05 00:00:00
## 10 2009-07-28 00:00:00 2009-10-02 00:00:00Data covers periods from 2009-05-06 to 2018-01-02 The year span is between 2009 to 2018. To check
sqlQuery("
SELECT DISTINCT(YEAR(launched)) as launchedyears
FROM campaign
ORDER BY launchedyears;
")## Warning: Closing open result sets## launchedyears
## 1 2009
## 2 2010
## 3 2011
## 4 2012
## 5 2013
## 6 2014
## 7 2015
## 8 2016
## 9 2017
## 10 2018Similarly, we can check the year span for deadline
sqlQuery("
SELECT DISTINCT(YEAR(deadline)) as deadlineyears
FROM campaign
ORDER BY deadlineyears;
")## Warning: Closing open result sets## deadlineyears
## 1 2009
## 2 2010
## 3 2011
## 4 2012
## 5 2013
## 6 2014
## 7 2015
## 8 2016
## 9 2017
## 10 2018We should extract year, month, and day of month
sqlQuery("
SELECT COUNT(*)
FROM campaign
WHERE YEAR(launched) = YEAR(deadline);
")## Warning: Closing open result sets## COUNT(*)
## 1 13923There are 13923 projects that the launched data and the deadline were in the same year.
sqlQuery("
SELECT COUNT(*)
FROM campaign
WHERE YEAR(launched) <> YEAR(deadline);
")## Warning: Closing open result sets## COUNT(*)
## 1 1077Therefore, there are 1077 projects whom the lauched year and the dealine year were different.
sqlQuery("
SELECT id, DATEDIFF(deadline, launched) as daynb, goal, pledged, backers, outcome
FROM campaign
ORDER BY daynb DESC
LIMIT 10
")## Warning: Closing open result sets## id daynb goal pledged backers outcome
## 1 6082 92 50000 1025.00 2 failed
## 2 6407 91 1500 1900.00 40 successful
## 3 10511 91 185 196.00 9 successful
## 4 13590 91 5000 951.00 17 failed
## 5 14870 91 10000 15.00 3 failed
## 6 4637 91 7500 6030.00 26 failed
## 7 14111 91 1000 2597.68 122 successful
## 8 550 91 1300 80.00 3 failed
## 9 5931 91 10000 10049.99 131 successful
## 10 6448 91 55000 5780.00 27 failedthe max duration is 91-92 days
sqlQuery("
SELECT id, DATEDIFF(deadline, launched) as daynb, goal, pledged, backers, outcome
FROM campaign
ORDER BY daynb ASC
LIMIT 10
")## Warning: Closing open result sets## id daynb goal pledged backers outcome
## 1 3936 1 20 20.00 1 successful
## 2 12640 1 8888 7670.00 86 failed
## 3 3233 1 100000000 0.00 0 failed
## 4 9672 1 100 320.00 47 successful
## 5 2260 1 500 500.00 7 successful
## 6 11246 1 8888 8385.00 71 failed
## 7 8311 2 400 464.66 34 successful
## 8 667 2 100 12701.00 101 successful
## 9 7947 3 25000 1.00 1 failed
## 10 10022 3 5000 26.00 2 failedThere some projects that launched and end at the same day Project 3233 had a goal of 100000000 and pledged 0 ==> failed
sqlQuery("
SELECT id, goal
FROM campaign
ORDER BY goal DESC
LIMIT 10;
")## Warning: Closing open result sets## id goal
## 1 12761 100000000
## 2 3233 100000000
## 3 7118 100000000
## 4 176 100000000
## 5 9944 100000000
## 6 8150 88767573
## 7 14319 44336067
## 8 6145 17708936
## 9 4574 11086826
## 10 3101 10000000What is the minium goal and the maximum goal?# the year span is between 2009 to 2018 it seems date the date column only contain date with the format yyyy-mm-dd (we have to change that)
sqlQuery("
SELECT MIN(goal) as min_goal,
MAX(goal) as max_goal
FROM campaign;
")## Warning: Closing open result sets## min_goal max_goal
## 1 0.75 1e+08The minimum is 0.75 (we do not know what currency yet) The maximun goal is 100 000 000 We can find which currency
sqlQuery("
SELECT name, currency_id, MAX(goal)
FROM campaign;
")## Warning: Closing open result sets## name currency_id MAX(goal)
## 1 Ragdolls 2 1e+08There are five projects that had a goal of 1e+08 (currency id is 2)
sqlQuery("
SELECT name
FROM currency
WHERE id = 2
")## Warning: Closing open result sets## name
## 1 USDTherefore, the maximum goal was 1e+08 USD. This query will be easier once we join tables. The goal 0.75 might be mistake?
sqlQuery("
SELECT name, currency_id, MIN(pledged)
FROM campaign;
")## Warning: Closing open result sets## name currency_id MIN(pledged)
## 1 Ragdolls 2 0there are some money for this project. it might be not a mistake
Similarly, we can find the maximum and the minimun of pledged.
sqlQuery("
SELECT MIN(pledged) as min_pledged,
MAX(pledged) as max_pledged
FROM campaign;
")## Warning: Closing open result sets## min_pledged max_pledged
## 1 0 5408917There is project that did not get any money. The maximum pledged is 5408917
Category table
sqlQuery("
SELECT *
FROM category
")## Warning: Closing open result sets## id name
## 1 1 Publishing
## 2 2 Film & Video
## 3 3 Music
## 4 4 Food
## 5 5 Design
## 6 6 Crafts
## 7 7 Games
## 8 8 Comics
## 9 9 Fashion
## 10 10 Theater
## 11 11 Art
## 12 12 Photography
## 13 13 Technology
## 14 14 Dance
## 15 15 JournalismThere are 15 categories
SubCategory table
sqlQuery("
SELECT *
FROM sub_category
")## Warning: Closing open result sets## id name category_id
## 1 1 Poetry 1
## 2 2 Narrative Film 2
## 3 3 Music 3
## 4 4 Film & Video 2
## 5 5 Restaurants 4
## 6 6 Food 4
## 7 7 Drinks 4
## 8 8 Product Design 5
## 9 9 Documentary 2
## 10 10 Nonfiction 1
## 11 11 Indie Rock 3
## 12 12 Crafts 6
## 13 13 Games 7
## 14 14 Tabletop Games 7
## 15 15 Design 5
## 16 16 Comic Books 8
## 17 17 Art Books 1
## 18 18 Fashion 9
## 19 19 Childrenswear 9
## 20 20 Theater 10
## 21 21 Comics 8
## 22 22 DIY 6
## 23 23 Webseries 2
## 24 24 Animation 2
## 25 25 Food Trucks 4
## 26 26 Public Art 11
## 27 27 Illustration 11
## 28 28 Photography 12
## 29 29 Pop 3
## 30 30 People 12
## 31 31 Art 11
## 32 32 Family 2
## 33 33 Fiction 1
## 34 34 Accessories 9
## 35 35 Rock 3
## 36 36 Hardware 13
## 37 37 Software 13
## 38 38 Weaving 6
## 39 39 Gadgets 13
## 40 40 Web 13
## 41 41 Jazz 3
## 42 42 Ready-to-wear 9
## 43 43 Festivals 10
## 44 44 Video Games 7
## 45 45 Anthologies 8
## 46 46 Publishing 1
## 47 47 Shorts 2
## 48 48 Electronic Music 3
## 49 49 Radio & Podcasts 1
## 50 50 Apps 13
## 51 51 Cookbooks 4
## 52 52 Apparel 9
## 53 53 Metal 3
## 54 54 Comedy 2
## 55 55 Hip-Hop 3
## 56 56 Periodicals 1
## 57 57 Dance 14
## 58 58 Technology 13
## 59 59 Painting 11
## 60 60 World Music 3
## 61 61 Photobooks 12
## 62 62 Drama 2
## 63 63 Architecture 5
## 64 64 Young Adult 1
## 65 65 Latin 3
## 66 66 Mobile Games 7
## 67 67 Flight 13
## 68 68 Fine Art 12
## 69 69 Action 2
## 70 70 Playing Cards 7
## 71 71 Makerspaces 13
## 72 72 Punk 3
## 73 73 Thrillers 2
## 74 74 Children's Books 1
## 75 75 Audio 15
## 76 76 Performance Art 11
## 77 77 Ceramics 11
## 78 78 Vegan 4
## 79 79 Graphic Novels 8
## 80 80 Fabrication Tools 13
## 81 81 Performances 14
## 82 82 Sculpture 11
## 83 83 Sound 13
## 84 84 Stationery 6
## 85 85 Print 15
## 86 86 Farmer's Markets 4
## 87 87 Events 4
## 88 88 Classical Music 3
## 89 89 Graphic Design 5
## 90 90 Spaces 4
## 91 91 Country & Folk 3
## 92 92 Wearables 13
## 93 93 Mixed Media 11
## 94 94 Journalism 15
## 95 95 Movie Theaters 2
## 96 96 Animals 12
## 97 97 Digital Art 11
## 98 98 Horror 2
## 99 99 Knitting 6
## 100 100 Small Batch 4
## 101 101 Installations 11
## 102 102 Community Gardens 4
## 103 103 DIY Electronics 13
## 104 104 Embroidery 6
## 105 105 Camera Equipment 13
## 106 106 Jewelry 9
## 107 107 Farms 4
## 108 108 Conceptual Art 11
## 109 109 Fantasy 2
## 110 110 Webcomics 8
## 111 111 Experimental 10
## 112 112 Science Fiction 2
## 113 113 Puzzles 7
## 114 114 R&B 3
## 115 115 Music Videos 2
## 116 116 Calendars 1
## 117 117 Video 15
## 118 118 Plays 10
## 119 119 Blues 3
## 120 120 Bacon 4
## 121 121 Faith 3
## 122 122 Live Games 7
## 123 123 Woodworking 6
## 124 124 Places 12
## 125 125 Footwear 9
## 126 126 3D Printing 13
## 127 127 Academic 1
## 128 128 Zines 1
## 129 129 Musical 10
## 130 130 Workshops 14
## 131 131 Photo 15
## 132 132 Immersive 10
## 133 133 Letterpress 6
## 134 134 Gaming Hardware 7
## 135 135 Candles 6
## 136 136 Television 2
## 137 137 Space Exploration 13
## 138 138 Couture 9
## 139 139 Nature 12
## 140 140 Robots 13
## 141 141 Typography 5
## 142 142 Crochet 6
## 143 143 Translations 1
## 144 144 Textiles 11
## 145 145 Pottery 6
## 146 146 Interactive Design 5
## 147 147 Video Art 11
## 148 148 Quilts 6
## 149 149 Glass 6
## 150 150 Pet Fashion 9
## 151 151 Printing 6
## 152 152 Romance 2
## 153 153 Civic Design 5
## 154 154 Kids 3
## 155 155 Literary Journals 1
## 156 156 Taxidermy 6
## 157 157 Literary Spaces 1
## 158 158 Chiptune 3
## 159 159 Residencies 14There are 159 subcategories
Currency table
sqlQuery("
SELECT *
FROM currency
")## Warning: Closing open result sets## id name
## 1 1 GBP
## 2 2 USD
## 3 3 CAD
## 4 4 AUD
## 5 5 NOK
## 6 6 EUR
## 7 7 MXN
## 8 8 SEK
## 9 9 NZD
## 10 10 CHF
## 11 11 DKK
## 12 12 HKD
## 13 13 SGD
## 14 14 JPYThere are 14 different curriencied.
Country table
sqlQuery("
SELECT *
FROM country
")## Warning: Closing open result sets## id name
## 1 1 GB
## 2 2 US
## 3 3 CA
## 4 4 AU
## 5 5 NO
## 6 6 IT
## 7 7 DE
## 8 8 IE
## 9 9 MX
## 10 10 ES
## 11 11 N,0"
## 12 12 SE
## 13 13 FR
## 14 14 NL
## 15 15 NZ
## 16 16 CH
## 17 17 AT
## 18 18 DK
## 19 19 BE
## 20 20 HK
## 21 21 LU
## 22 22 SG
## 23 23 JPThere are 23 countries. Obviously, some countries use the same currency. There is a mistake on country name (at id = 11): N,0"
sqlQuery("
SELECT id, name, country_id, currency_id
FROM campaign
WHERE country_id = 11
LIMIT 10;
")## Warning: Closing open result sets## id name country_id
## 1 91 Metro760 LGBT Newspaper for the Palm Springs CA area 11
## 2 471 Chronicles: Arcane Tales 11
## 3 589 Boketto Makes Their First Record! 11
## 4 615 Joe Applehead 11
## 5 658 Chasing Tails - The Amberland Music Video 11
## 6 722 Breakin' Chainz New Single Release by Art Brown 11
## 7 849 Project cancelled (Canceled) 11
## 8 1079 Radio Vid 11
## 9 1173 Susan T. Mashiyama's Debut CD: "Dance of the Fairies" 11
## 10 1309 Help Judy Record Fresh EP & Go To Haiti- Music w Orphans! 11
## currency_id
## 1 2
## 2 2
## 3 2
## 4 2
## 5 1
## 6 2
## 7 1
## 8 2
## 9 2
## 10 2The country N,0" (id = 11) use USD and GBP at the same time. So far we can not deduct which country is we can look for project with the same countries id but use two different currencies If we take a deeper look, let see 100 records
sqlQuery("
SELECT id, name, country_id, currency_id
FROM campaign
WHERE country_id = 11
LIMIT 100;
")## Warning: Closing open result sets## id name
## 1 91 Metro760 LGBT Newspaper for the Palm Springs CA area
## 2 471 Chronicles: Arcane Tales
## 3 589 Boketto Makes Their First Record!
## 4 615 Joe Applehead
## 5 658 Chasing Tails - The Amberland Music Video
## 6 722 Breakin' Chainz New Single Release by Art Brown
## 7 849 Project cancelled (Canceled)
## 8 1079 Radio Vid
## 9 1173 Susan T. Mashiyama's Debut CD: "Dance of the Fairies"
## 10 1309 Help Judy Record Fresh EP & Go To Haiti- Music w Orphans!
## 11 1913 Website that showcases Local Musicians and Venues
## 12 1935 South West UK motorsport coverage by Cars on film for 2015
## 13 1938 New EP/Music Development
## 14 2403 B. Starr is ready to tour and record 2nd studio album #indie
## 15 2534 Time Toupee
## 16 2676 Imagineer Records Studio Upgrade
## 17 2820 1 Drop Nation EP Fundraiser
## 18 2944 Operation Make "Annie" Tracks!
## 19 3129 Green is Magic!
## 20 3230 Science Breakdown
## 21 3351 Bobby Jasinski Presents: Retrospection
## 22 3376 Summer Osborne NEW ALBUM Spiritual Revolution
## 23 3386 Hwy Lions Recording at Foxtrot Studios
## 24 3482 The First light
## 25 3668 .Challenge Accepted
## 26 3702 Home
## 27 3753 AT ALL COST!
## 28 3789 You & Who
## 29 3829 An Album of Firsts
## 30 3851 ensemble, et al. second full-length album —with STRETCH GOAL
## 31 3888 Mick Jenkins Feature
## 32 3905 Festival in a Box - replacement marquee and stage needed
## 33 4094 Zombie Are Real
## 34 4122 Wonderland, The Album
## 35 4170 Daniel Corozza - Allegations Of A Conversation
## 36 4179 ME (men evolution)
## 37 4200 Newburgh: Beauty and Tragedy
## 38 4222 CHARLY'S WORLD
## 39 4280 The Day I Discovered Just How Truly Special I Am
## 40 4297 Unique Guitar Rhythm Lesson Series for ALL levels
## 41 4424 Butterfield VHS Tape Recovery Project
## 42 4494 The Making of Muddy Belle's Second EP
## 43 4549 Custom guitars & amps
## 44 4646 Rick Wilkerson Entertainment
## 45 4664 TIME OUT Season One
## 46 4748 MOUSE - a feature film directed by Dusty Bias
## 47 4968 Pocket The Fool: Faire Enough
## 48 5011 The Please Please Me: Great Music for Hard Times
## 49 5247 Help 3PM travel with Warped Tour!
## 50 5333 Bookworm Effects Guitar Pedals
## 51 5449 Mission Underground LA 2015: TeamBackPack Cypher (MULA)
## 52 5533 A Life In 12 Chapters
## 53 5601 Who are you? – Feel the Great Shift of Awakening
## 54 5651 Kito Rodriguez- New Reality- mastering and video campaign.
## 55 5663 Help fund the release of my album Queen Maud Land!
## 56 5745 Runaway John - Debut EP
## 57 5833 The Next Day: In Three Parts
## 58 5890 BU Sweethearts A Cappella Album
## 59 5899 Support aspiring author/poet in printing debut book
## 60 5973 M2 Arts
## 61 6080 Travelling through the Rainbow
## 62 6084 Be a Dr. San Guinary Creature Feature Horror Helper, 2014
## 63 6092 The Next Step (Mixtape)
## 64 6219 IndieHarbor, A true indie artist's platform.
## 65 6267 A Wonderful Chat With An Angel
## 66 6293 #SavingJustin "When I Sing"
## 67 6521 Carlsbad Music Festival 11th Annual Season
## 68 6614 GareFest
## 69 6698 Surrender
## 70 6738 LUME Festival 2016
## 71 6872 Let's Change The World
## 72 6966 Meet Me By The Moon
## 73 7047 HEARTS & VEINS
## 74 7307 Album Release und Musik Video
## 75 7341 BYOB (Brew Your Own Batch) Logbook for craft beer brewing!
## 76 7366 Echo Wants Her Voice Back - Debut EP... Get INVOLVED!
## 77 7517 Ruby Bells
## 78 7850 Only The Young Die Good (film soundtrack)
## 79 7907 Tim Carey & Amanda Taylor - First EP
## 80 7956 Remnants (75 min)
## 81 7983 Amnesiac
## 82 8089 The Human Instruction Manual: The answers you seek are here!
## 83 8136 Unlock "The Black Garden Diaries"!
## 84 8229 Help me raise \2001090 for Numark NS7ii Scratching
## 85 8337 STOP THE POLICE!
## 86 8425 The Rub
## 87 8434 SONG OF DINA
## 88 8520 BARS brings to life the work of women composers
## 89 8980 Environ-Health Mini: Radiation Emergency - Survival Guide
## 90 9020 Guilt Trip Stoked//Broke Tour
## 91 9103 Nick Kody EP - Post Production Costs
## 92 9145 Chelsea Saddler's new album "The Offering"
## 93 9178 Bring Some Famed Musicians to an ALS event
## 94 9205 1 & 3 Productions Radio Station/Studio Fund
## 95 9210 I'm In Love With Your Husband, Adultery Exposed.
## 96 9309 my B.I.
## 97 9482 Nick Capaldi does Nashville the vinyl way
## 98 9516 A Christmas Album made by the Pure Witness Band!
## 99 9571 Making A Music Studio
## 100 9586 Daryl Shawn: ON TIME | international fingerstyle guitar
## country_id currency_id
## 1 11 2
## 2 11 2
## 3 11 2
## 4 11 2
## 5 11 1
## 6 11 2
## 7 11 1
## 8 11 2
## 9 11 2
## 10 11 2
## 11 11 2
## 12 11 1
## 13 11 2
## 14 11 2
## 15 11 2
## 16 11 2
## 17 11 9
## 18 11 2
## 19 11 2
## 20 11 2
## 21 11 2
## 22 11 2
## 23 11 2
## 24 11 4
## 25 11 2
## 26 11 2
## 27 11 2
## 28 11 2
## 29 11 2
## 30 11 2
## 31 11 2
## 32 11 1
## 33 11 3
## 34 11 1
## 35 11 2
## 36 11 2
## 37 11 2
## 38 11 1
## 39 11 2
## 40 11 2
## 41 11 2
## 42 11 2
## 43 11 2
## 44 11 2
## 45 11 2
## 46 11 2
## 47 11 2
## 48 11 2
## 49 11 2
## 50 11 2
## 51 11 2
## 52 11 2
## 53 11 2
## 54 11 2
## 55 11 6
## 56 11 6
## 57 11 2
## 58 11 2
## 59 11 2
## 60 11 2
## 61 11 4
## 62 11 2
## 63 11 2
## 64 11 3
## 65 11 2
## 66 11 2
## 67 11 2
## 68 11 2
## 69 11 3
## 70 11 1
## 71 11 2
## 72 11 2
## 73 11 2
## 74 11 6
## 75 11 2
## 76 11 1
## 77 11 2
## 78 11 4
## 79 11 2
## 80 11 2
## 81 11 2
## 82 11 2
## 83 11 2
## 84 11 6
## 85 11 2
## 86 11 2
## 87 11 1
## 88 11 2
## 89 11 2
## 90 11 4
## 91 11 2
## 92 11 2
## 93 11 2
## 94 11 2
## 95 11 2
## 96 11 2
## 97 11 1
## 98 11 3
## 99 11 2
## 100 11 2So, there are some projects that funded by people from diffent countries To check
sqlQuery("
SELECT country_id, COUNT(country_id), currency_id, COUNT(currency_id)
FROM campaign
GROUP BY country_id, currency_id
ORDER BY country_id;
")## Warning: Closing open result sets## country_id COUNT(country_id) currency_id COUNT(currency_id)
## 1 1 1304 1 1304
## 2 2 11649 2 11649
## 3 3 588 3 588
## 4 4 313 4 313
## 5 5 26 5 26
## 6 6 121 6 121
## 7 7 148 6 148
## 8 8 36 6 36
## 9 9 70 7 70
## 10 10 84 6 84
## 11 11 13 1 13
## 12 11 123 2 123
## 13 11 4 3 4
## 14 11 6 4 6
## 15 11 5 6 5
## 16 11 1 8 1
## 17 11 2 9 2
## 18 11 1 10 1
## 19 12 67 8 67
## 20 13 106 6 106
## 21 14 104 6 104
## 22 15 57 9 57
## 23 16 35 10 35
## 24 17 27 6 27
## 25 18 48 11 48
## 26 19 16 6 16
## 27 20 24 12 24
## 28 21 3 6 3
## 29 22 19 13 19For countries with id from 1 to 10, 12 to 22, the country_id and the currency_id are consistent. In contrast, country id with id of 11, the currencies that have been use include 1,2,3,4,8,9,10. There are 123 records that show that this project (country 11 is origin country) is funded using USD (currency id = 2)
Question 1: What country has the heighest number of successful/failed campains
sqlQuery("
SELECT country.id, country.name, COUNT(campaign.country_id)
FROM campaign, country
WHERE campaign.country_id = country.id
AND campaign.outcome = 'successful'
GROUP BY country_id
ORDER BY COUNT(campaign.country_id) DESC;
")## Warning: Closing open result sets## id name COUNT(campaign.country_id)
## 1 2 US 4365
## 2 1 GB 487
## 3 3 CA 137
## 4 4 AU 84
## 5 7 DE 36
## 6 13 FR 29
## 7 14 NL 28
## 8 9 MX 20
## 9 18 DK 16
## 10 12 SE 15
## 11 6 IT 15
## 12 15 NZ 14
## 13 10 ES 13
## 14 8 IE 10
## 15 16 CH 10
## 16 20 HK 8
## 17 22 SG 8
## 18 5 NO 8
## 19 11 N,0" 7
## 20 19 BE 4
## 21 17 AT 3
## 22 21 LU 2US had the highest number of sucessful campaigns LU only had two campaigns that were successful.
sqlQuery("
SELECT country.id, country.name, COUNT(campaign.country_id)
FROM campaign, country
WHERE campaign.country_id = country.id
AND campaign.outcome = 'failed'
GROUP BY country_id
ORDER BY COUNT(campaign.country_id) DESC;
")## Warning: Closing open result sets## id name COUNT(campaign.country_id)
## 1 2 US 6075
## 2 1 GB 669
## 3 3 CA 355
## 4 4 AU 170
## 5 6 IT 94
## 6 7 DE 90
## 7 14 NL 56
## 8 13 FR 56
## 9 10 ES 51
## 10 12 SE 44
## 11 9 MX 35
## 12 15 NZ 30
## 13 16 CH 23
## 14 8 IE 21
## 15 18 DK 20
## 16 17 AT 17
## 17 20 HK 11
## 18 5 NO 10
## 19 22 SG 10
## 20 19 BE 6
## 21 11 N,0" 6
## 22 21 LU 1US also had the highest number of failed projects.
Now, we are only interested in projects founded in US
sqlQuery("
SELECT outcome, COUNT(outcome)
FROM campaign
WHERE country_id = 2
group by outcome;
")## Warning: Closing open result sets## outcome COUNT(outcome)
## 1 failed 6075
## 2 successful 4365
## 3 canceled 1088
## 4 suspended 52
## 5 live 69There 6075 projects that failed, 4365 were successful, 1088 were canceled, About 100 projects that were suspended and live.
the top 10 popular subcategories (As we do not have the column category)
sqlQuery("
SELECT count(*), sub.name
FROM campaign AS camp, sub_category AS sub
WHERE sub.id = camp.sub_category_id
AND camp.country_id = 2
GROUP BY camp.sub_category_id
ORDER BY COUNT(*) DESC
LIMIT 10;
")## Warning: Closing open result sets## count(*) name
## 1 707 Product Design
## 2 586 Documentary
## 3 462 Music
## 4 433 Shorts
## 5 418 Tabletop Games
## 6 358 Food
## 7 323 Film & Video
## 8 312 Nonfiction
## 9 305 Fiction
## 10 291 Video GamesSo far, we do not have the column category in the campain table. Product Design is the most popular subcategory.
***Duration and outcome
sqlQuery("
SELECT
SUM(CASE
WHEN TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 14
THEN 1 ELSE 0 END) AS less2weeks,
SUM(CASE
WHEN (TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) >= 15
AND TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 28)
THEN 1 ELSE 0 END) as 2_4weeks,
SUM(CASE
WHEN (TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) >= 29
AND TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 42)
THEN 1 ELSE 0 END) as 4_6weeks,
SUM(CASE
WHEN (TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) > 42
AND TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 56)
THEN 1 ELSE 0 END) as 6_8weeks,
SUM(CASE
WHEN (TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) > 56
AND TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 70)
THEN 1 ELSE 0 END) as 8_10weeks,
SUM(CASE
WHEN (TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) > 70)
THEN 1 ELSE 0 END) as greater10weeks,
COUNT(*) as total, outcome
FROM campaign
WHERE campaign.country_id = 2
AND campaign.outcome = 'successful' OR campaign.outcome = 'failed' OR campaign.outcome = 'canceled'
GROUP BY campaign.outcome;
")## Warning in .local(conn, statement, ...): Decimal MySQL column 0 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 5 imported as
## numeric## Warning: Closing open result sets## less2weeks 2_4weeks 4_6weeks 6_8weeks 8_10weeks greater10weeks total
## 1 236 835 5039 689 965 86 7850
## 2 192 756 2719 371 267 60 4365
## 3 35 133 968 159 200 18 1513
## outcome
## 1 failed
## 2 successful
## 3 canceledsqlQuery("
SELECT
SUM(CASE
WHEN TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 28
THEN 1 ELSE 0 END) AS less4weeks,
SUM(CASE
WHEN (TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) > 28
AND TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) <= 56)
THEN 1
ELSE 0 END) as 4_8weeks,
SUM(CASE
WHEN TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) > 56
THEN 1 ELSE 0 END) as higher8weeks,
COUNT(*) as total, outcome
FROM campaign
WHERE campaign.country_id = 2
AND campaign.outcome = 'successful' OR campaign.outcome = 'failed' OR campaign.outcome = 'canceled'
GROUP BY campaign.outcome;
")## Warning in .local(conn, statement, ...): Decimal MySQL column 0 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric## Warning: Closing open result sets## less4weeks 4_8weeks higher8weeks total outcome
## 1 1071 5728 1051 7850 failed
## 2 948 3090 327 4365 successful
## 3 168 1127 218 1513 canceledNumber of bakers and outcome
sqlQuery("
SELECT
SUM(CASE WHEN backers <= 25 THEN 1 ELSE 0 END) AS less10,
SUM(CASE WHEN (backers > 25 AND backers <= 50) THEN 1 ELSE 0 END) AS b10_50,
SUM(CASE WHEN (backers > 50 AND backers <= 75) THEN 1 ELSE 0 END) AS b50_75,
SUM(CASE WHEN (backers > 75 AND backers <= 100) THEN 1 ELSE 0 END) AS b75_100,
SUM(CASE WHEN backers > 100 THEN 1 ELSE 0 END) as higher100,
COUNT(*) as total, outcome
FROM campaign
WHERE campaign.country_id = 2
AND campaign.outcome = 'successful' OR campaign.outcome = 'failed' OR campaign.outcome = 'canceled'
GROUP BY campaign.outcome;
")## Warning in .local(conn, statement, ...): Decimal MySQL column 0 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
## numeric## Warning: Closing open result sets## less10 b10_50 b50_75 b75_100 higher100 total outcome
## 1 6710 575 233 108 224 7850 failed
## 2 755 896 627 385 1702 4365 successful
## 3 1245 117 52 33 66 1513 canceledless than 10 ==> failed or canceled higher than 100 ==> successful
Goal and outcome
sqlQuery("
SELECT
SUM(CASE WHEN goal <= 5000 THEN 1 ELSE 0 END) AS g5K,
SUM(CASE WHEN (goal > 5000 AND goal <= 25000) THEN 1 ELSE 0 END) AS g25K,
SUM(CASE WHEN (goal > 25000 AND goal <= 50000) THEN 1 ELSE 0 END) AS g50K,
SUM(CASE WHEN (goal > 50000 AND goal <= 75000) THEN 1 ELSE 0 END) AS g75K,
SUM(CASE WHEN (goal > 75000 AND goal <= 100000) THEN 1 ELSE 0 END) AS g100K,
SUM(CASE WHEN (goal > 100000 AND goal <= 200000) THEN 1 ELSE 0 END) AS g200K,
SUM(CASE WHEN goal > 200000 THEN 1 ELSE 0 END) as higher200K,
COUNT(*) as total, outcome
FROM campaign
WHERE campaign.country_id = 2
AND campaign.outcome = 'successful' OR campaign.outcome = 'failed' OR campaign.outcome = 'canceled'
GROUP BY campaign.outcome;
")## Warning in .local(conn, statement, ...): Decimal MySQL column 0 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 4 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 5 imported as
## numeric## Warning in .local(conn, statement, ...): Decimal MySQL column 6 imported as
## numeric## Warning: Closing open result sets## g5K g25K g50K g75K g100K g200K higher200K total outcome
## 1 3451 2845 773 231 191 169 190 7850 failed
## 2 2644 1423 202 36 39 12 9 4365 successful
## 3 569 518 195 66 69 36 60 1513 canceledsuccessful : goal less than 25K May need to categorize
sqlQuery("
SELECT category.id,
ROUND(AVG(c.goal)) as Avggoal,
ROUND(AVG(c.pledged)) as Avgpledged,
ROUND(AVG(c.backers)),
ROUND(AVG(c.pledged))-ROUND(AVG(c.goal)) as gain,
c.outcome
FROM campaign c LEFT JOIN sub_category ON c.sub_category_id = sub_category.id
LEFT JOIN category ON sub_category.category_id = category.id
WHERE c.outcome = 'successful' OR c.outcome = 'failed' OR c.outcome = 'canceled'
AND country_id = 2
GROUP BY category.id, c.outcome
ORDER BY category.id, gain;
")## Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
## numeric## Warning: Closing open result sets## id Avggoal Avgpledged ROUND(AVG(c.backers)) gain outcome
## 1 1 127788 579 10 -127209 failed
## 2 1 13804 498 10 -13306 canceled
## 3 1 6268 9020 147 2752 successful
## 4 2 202779 1614 17 -201165 failed
## 5 2 103468 1047 12 -102421 canceled
## 6 2 12506 17833 208 5327 successful
## 7 3 21711 616 10 -21095 failed
## 8 3 10883 319 6 -10564 canceled
## 9 3 5422 6735 101 1313 successful
## 10 4 33725 425 6 -33300 canceled
## 11 4 28306 997 11 -27309 failed
## 12 4 11105 15876 152 4771 successful
## 13 5 55933 2882 31 -53051 failed
## 14 5 29266 2724 31 -26542 canceled
## 15 5 14902 50114 554 35212 successful
## 16 6 10074 324 5 -9750 failed
## 17 6 10644 1135 15 -9509 canceled
## 18 6 2911 5232 97 2321 successful
## 19 7 55106 2791 46 -52315 canceled
## 20 7 32928 3036 55 -29892 failed
## 21 7 14018 54617 793 40599 successful
## 22 8 13827 1125 31 -12702 canceled
## 23 8 10080 1200 19 -8880 failed
## 24 8 5600 10224 213 4624 successful
## 25 9 50075 1078 11 -48997 failed
## 26 9 16261 1438 16 -14823 canceled
## 27 9 10708 21739 215 11031 successful
## 28 10 33967 213 2 -33754 canceled
## 29 10 25521 627 8 -24894 failed
## 30 10 4695 5138 64 443 successful
## 31 11 92660 742 10 -91918 failed
## 32 11 46966 840 13 -46126 canceled
## 33 11 4485 10339 87 5854 successful
## 34 12 14055 188 4 -13867 canceled
## 35 12 12499 1259 15 -11240 failed
## 36 12 6322 8479 98 2157 successful
## 37 13 219705 2416 21 -217289 failed
## 38 13 68056 8468 39 -59588 canceled
## 39 13 28957 99166 1224 70209 successful
## 40 14 5830 835 11 -4995 failed
## 41 14 5200 889 15 -4311 canceled
## 42 14 4838 5526 68 688 successful
## 43 15 21305 351 6 -20954 failed
## 44 15 7625 5 1 -7620 canceled
## 45 15 10515 13674 184 3159 successfulsqlQuery("
SELECT
campaign.id AS id,
campaign.name AS ProjectName,
category.id AS CategoryID,
category.name AS Category,
campaign.sub_category_id AS SubCategoryId,
sub_category.name AS SubCategory,
campaign.goal AS goal,
campaign.pledged AS pledged,
campaign.backers AS backers,
campaign.launched AS launched,
campaign.launched AS deadline,
TIMESTAMPDIFF(DAY,campaign.launched,campaign.deadline) AS days,
campaign.outcome AS Outcomes
FROM campaign LEFT JOIN sub_category ON campaign.sub_category_id = sub_category.id
LEFT JOIN category ON sub_category.category_id = category.id
WHERE campaign.outcome = 'successful' OR campaign.outcome = 'failed'
AND country_id = 2 # USA
ORDER BY campaign.id ASC
LIMIT 20;
")## Warning: Closing open result sets## id ProjectName CategoryID
## 1 1 Ragdolls 2
## 2 2 One To Ten 2
## 3 3 Future Gun - A short film about kids and advanced hardware 2
## 4 4 Fourth Wave Apparel—Vintage Fashion for the Modern Feminist 9
## 5 5 The Rich Lifestyle 2
## 6 6 Perils Galore! 11
## 7 7 In a Dark Dark House - Directed by Larry Moss 10
## 8 8 CHARLIEFOXTROTNY 2
## 9 9 Fat Englishmen - Beer and Sumo 2
## 10 10 bear elements old-fashioned shave and beard care for men 6
## 11 11 DigIndustrial Art 11
## 12 12 Expanding Farm to service the local Food Bank 4
## 13 13 Church Hive. Church hiring made easy! 13
## 14 14 The Visual Media Directory Project 11
## 15 15 Too Sweet - The Not So Serious Side to Diabetes 1
## 16 16 “Baby Hungry” Short Film Finishing Funds 2
## 17 17 Turn "solvents-forgive yr. blood" into 12 inches of vinyl!! 3
## 18 18 Fishin' for Chickens 2
## 19 19 ROAM RECORD FUND 3
## 20 20 William Charles and Dirt Road Trippers NEW EP 3
## Category SubCategoryId SubCategory goal pledged backers
## 1 Film & Video 23 Webseries 15000.00 20.00 3
## 2 Film & Video 47 Shorts 223.68 413.81 23
## 3 Film & Video 24 Animation 5000.00 1497.00 28
## 4 Fashion 52 Apparel 6000.00 8795.00 218
## 5 Film & Video 9 Documentary 2000000.00 2.00 2
## 6 Art 27 Illustration 2500.00 230.00 5
## 7 Theater 20 Theater 55000.00 9316.00 45
## 8 Film & Video 23 Webseries 6000.00 161.00 4
## 9 Film & Video 54 Comedy 50000.00 0.00 0
## 10 Crafts 12 Crafts 12000.00 306.00 11
## 11 Art 97 Digital Art 30000.00 111.00 4
## 12 Food 107 Farms 2500.00 405.00 5
## 13 Technology 40 Web 16800.00 101.00 2
## 14 Art 26 Public Art 10000.00 2.00 2
## 15 Publishing 10 Nonfiction 2500.00 2525.00 6
## 16 Film & Video 47 Shorts 1500.00 1525.00 23
## 17 Music 3 Music 1000.00 1494.00 43
## 18 Film & Video 47 Shorts 2695.00 3667.13 75
## 19 Music 35 Rock 5000.00 5847.00 99
## 20 Music 35 Rock 800.00 425.00 8
## launched deadline days Outcomes
## 1 2013-04-25 00:00:00 2013-04-25 00:00:00 30 failed
## 2 2015-11-16 00:00:00 2015-11-16 00:00:00 30 successful
## 3 2013-08-28 00:00:00 2013-08-28 00:00:00 30 failed
## 4 2014-07-22 00:00:00 2014-07-22 00:00:00 30 successful
## 5 2016-08-24 00:00:00 2016-08-24 00:00:00 35 failed
## 6 2012-09-25 00:00:00 2012-09-25 00:00:00 37 failed
## 7 2014-07-01 00:00:00 2014-07-01 00:00:00 45 failed
## 8 2014-05-21 00:00:00 2014-05-21 00:00:00 30 failed
## 9 2015-03-03 00:00:00 2015-03-03 00:00:00 30 failed
## 10 2016-04-14 00:00:00 2016-04-14 00:00:00 30 failed
## 11 2014-06-19 00:00:00 2014-06-19 00:00:00 20 failed
## 12 2016-06-13 00:00:00 2016-06-13 00:00:00 30 failed
## 13 2016-09-01 00:00:00 2016-09-01 00:00:00 30 failed
## 14 2012-03-07 00:00:00 2012-03-07 00:00:00 60 failed
## 15 2014-01-16 00:00:00 2014-01-16 00:00:00 30 successful
## 16 2010-10-27 00:00:00 2010-10-27 00:00:00 30 successful
## 17 2011-04-20 00:00:00 2011-04-20 00:00:00 30 successful
## 18 2011-11-14 00:00:00 2011-11-14 00:00:00 16 successful
## 19 2010-09-29 00:00:00 2010-09-29 00:00:00 30 successful
## 20 2012-10-09 00:00:00 2012-10-09 00:00:00 60 failed