| | 99 | == Процентуален извештај за артисти и понуди сортирани по тип на артист |
| | 100 | |
| | 101 | {{{#!sql |
| | 102 | select |
| | 103 | *, |
| | 104 | ( |
| | 105 | select to_char( |
| | 106 | ( |
| | 107 | select |
| | 108 | count(*) |
| | 109 | from artists a |
| | 110 | where |
| | 111 | a.admin_verified_at is not null and |
| | 112 | a.artist_type_id = at1.id |
| | 113 | ) |
| | 114 | / |
| | 115 | ( |
| | 116 | select count(*) from artists a |
| | 117 | where |
| | 118 | a.admin_verified_at is not null |
| | 119 | )::numeric , |
| | 120 | 'fm00D00%' |
| | 121 | ) |
| | 122 | ) as percent_artists, |
| | 123 | ( |
| | 124 | select to_char( |
| | 125 | ( |
| | 126 | select |
| | 127 | count(*) |
| | 128 | from artists a |
| | 129 | where |
| | 130 | a.admin_verified_at is not null and |
| | 131 | a.artist_type_id = at1.id and |
| | 132 | a.manager_id is not null |
| | 133 | ) |
| | 134 | / |
| | 135 | ( |
| | 136 | select count(*) from artists a |
| | 137 | where |
| | 138 | a.admin_verified_at is not null |
| | 139 | )::numeric * 100, |
| | 140 | 'fm00D00%' |
| | 141 | ) |
| | 142 | )as percent_artists_with_managers, |
| | 143 | ( |
| | 144 | select |
| | 145 | count(*) |
| | 146 | from artists a |
| | 147 | join offers o on o.artist_id = a.user_id |
| | 148 | where |
| | 149 | a.admin_verified_at is not null and |
| | 150 | a.artist_type_id = at1.id |
| | 151 | ) as num_offers_received, |
| | 152 | ( |
| | 153 | to_char |
| | 154 | ( |
| | 155 | ( |
| | 156 | case when ( |
| | 157 | select |
| | 158 | count(*) |
| | 159 | from artists a |
| | 160 | join offers o on o.artist_id = a.user_id |
| | 161 | where |
| | 162 | a.admin_verified_at is not null and |
| | 163 | a.artist_type_id = at1.id |
| | 164 | ) != 0 then |
| | 165 | ( |
| | 166 | select |
| | 167 | count(*) |
| | 168 | from artists a |
| | 169 | join offers o on o.artist_id = a.user_id |
| | 170 | where |
| | 171 | a.admin_verified_at is not null and |
| | 172 | a.artist_type_id = at1.id and |
| | 173 | o.status = 'COMPLETED' |
| | 174 | ) |
| | 175 | / |
| | 176 | ( |
| | 177 | select |
| | 178 | count(*) |
| | 179 | from artists a |
| | 180 | join offers o on o.artist_id = a.user_id |
| | 181 | where |
| | 182 | a.admin_verified_at is not null and |
| | 183 | a.artist_type_id = at1.id |
| | 184 | )::numeric |
| | 185 | else |
| | 186 | 0::numeric |
| | 187 | end |
| | 188 | ) * 100, |
| | 189 | 'fm00D00%' |
| | 190 | ) |
| | 191 | ) as percent_completed_offers, -- procent na 'COMPLETED' offers |
| | 192 | ( |
| | 193 | to_char |
| | 194 | ( |
| | 195 | ( |
| | 196 | case when ( |
| | 197 | select |
| | 198 | count(*) |
| | 199 | from artists a |
| | 200 | join offers o on o.artist_id = a.user_id |
| | 201 | where |
| | 202 | a.admin_verified_at is not null and |
| | 203 | a.artist_type_id = at1.id |
| | 204 | ) != 0 then |
| | 205 | ( |
| | 206 | select |
| | 207 | count(*) |
| | 208 | from artists a |
| | 209 | join offers o on o.artist_id = a.user_id |
| | 210 | where |
| | 211 | a.admin_verified_at is not null and |
| | 212 | a.artist_type_id = at1.id and |
| | 213 | o.status = 'IN PROGRESS' |
| | 214 | ) |
| | 215 | / |
| | 216 | ( |
| | 217 | select |
| | 218 | count(*) |
| | 219 | from artists a |
| | 220 | join offers o on o.artist_id = a.user_id |
| | 221 | where |
| | 222 | a.admin_verified_at is not null and |
| | 223 | a.artist_type_id = at1.id |
| | 224 | )::numeric |
| | 225 | else |
| | 226 | 0::numeric |
| | 227 | end |
| | 228 | ) * 100, |
| | 229 | 'fm00D00%' |
| | 230 | ) |
| | 231 | ) as percent_inProgress_offers, -- procent na 'IN PROGRESS' offers |
| | 232 | ( |
| | 233 | to_char |
| | 234 | ( |
| | 235 | ( |
| | 236 | case when ( |
| | 237 | select |
| | 238 | count(*) |
| | 239 | from artists a |
| | 240 | join offers o on o.artist_id = a.user_id |
| | 241 | where |
| | 242 | a.admin_verified_at is not null and |
| | 243 | a.artist_type_id = at1.id |
| | 244 | ) != 0 then |
| | 245 | ( |
| | 246 | select |
| | 247 | count(*) |
| | 248 | from artists a |
| | 249 | join offers o on o.artist_id = a.user_id |
| | 250 | where |
| | 251 | a.admin_verified_at is not null and |
| | 252 | a.artist_type_id = at1.id and |
| | 253 | o.status = 'WAITING FOR PAYMENT' |
| | 254 | ) |
| | 255 | / |
| | 256 | ( |
| | 257 | select |
| | 258 | count(*) |
| | 259 | from artists a |
| | 260 | join offers o on o.artist_id = a.user_id |
| | 261 | where |
| | 262 | a.admin_verified_at is not null and |
| | 263 | a.artist_type_id = at1.id |
| | 264 | )::numeric |
| | 265 | else |
| | 266 | 0::numeric |
| | 267 | end |
| | 268 | ) * 100, |
| | 269 | 'fm00D00%' |
| | 270 | ) |
| | 271 | ) as percent_waitingForPayment_offers, -- procent na 'WAITING FOR PAYMENT' offers |
| | 272 | ( |
| | 273 | to_char |
| | 274 | ( |
| | 275 | ( |
| | 276 | case when ( |
| | 277 | select |
| | 278 | count(*) |
| | 279 | from artists a |
| | 280 | join offers o on o.artist_id = a.user_id |
| | 281 | where |
| | 282 | a.admin_verified_at is not null and |
| | 283 | a.artist_type_id = at1.id |
| | 284 | ) != 0 then |
| | 285 | ( |
| | 286 | select |
| | 287 | count(*) |
| | 288 | from artists a |
| | 289 | join offers o on o.artist_id = a.user_id |
| | 290 | where |
| | 291 | a.admin_verified_at is not null and |
| | 292 | a.artist_type_id = at1.id and |
| | 293 | o.status = 'DECLINED' |
| | 294 | ) |
| | 295 | / |
| | 296 | ( |
| | 297 | select |
| | 298 | count(*) |
| | 299 | from artists a |
| | 300 | join offers o on o.artist_id = a.user_id |
| | 301 | where |
| | 302 | a.admin_verified_at is not null and |
| | 303 | a.artist_type_id = at1.id |
| | 304 | )::numeric |
| | 305 | else |
| | 306 | 0::numeric |
| | 307 | end |
| | 308 | ) * 100, |
| | 309 | 'fm00D00%' |
| | 310 | ) |
| | 311 | ) as percent_declined_offers -- procent na 'DECLINED' offers |
| | 312 | from artist_types at1 |
| | 313 | order by at1.id |
| | 314 | }}} |
| | 315 | |
| | 316 | |
