Untitled

mail@pastecode.io avatar
unknown
mysql
a year ago
6.3 kB
1
Indexable
Never
SELECT AVG(CAST(network_logs_size AS FLOAT64)) AS ADOBE_NETWORK_LOG_SIZE_PER_SESSION FROM
(SELECT 
    IF(SUBSTR(JSON_EXTRACT(feature_usage, '$.networkLogsV2.size'), 1, 1) = '[', 
      JSON_EXTRACT(feature_usage, '$.networkLogsV2.size[0]'), 
      JSON_EXTRACT(feature_usage, '$.networkLogsV2.size')
    ) AS network_logs_size
  FROM 
    `browserstack-production.automate.automate_test_sessions_partitioned`
  WHERE 
    (DATE(_PARTITIONTIME) >= "2023-03-01" AND DATE(_PARTITIONTIME) < "2023-04-01") 
    AND JSON_EXTRACT(feature_usage, '$.networkLogsV2.size') IS NOT NULL
    AND (LOWER(JSON_EXTRACT(capabilities, "$['browserstack.networkLogsOptions.captureContent']")) IS NULL
    OR LOWER(JSON_EXTRACT(capabilities, "$['browserstack.networkLogsOptions']['captureContent']")) IS NULL)
    AND group_id in (2902477,4800345,3627183,6599960,3383373,5896486,3731261,5020921,4368449,4633051,4769837,5317164,846753,4634297,2342234,3642495,5288328,4660019,4304657,3957634,2731727,5169316,2425523,6902204,5831324,2487984,732519,1550179,3859622,2285905,6376217,6135920,5586758,3931103,5290292,5854725,1071836,4291873,5598314,2916114,4972024,4655452,1224686,531937,404636,1777184,4882729,4716945,4646980,3742775,2881256,4915677,5033096,4823354,2293853,4209289,3966329,4695844,4770052,5440778,1484680,4784201,2293092,4146235,5820942,1769454,5112505,4560456,787771,4733819,4490381,5339984,1755108,3107005,4264698,3279150,6681258,89680,1765615,1287372,3442840,2690752,37862,51064,4302464,125988,2382329,3326986,4713142,3959640,2911890,2575417,1319857,4589296,4488178,3486178,3034471,3236614,4929136,2768570,4035564,4197291,3142027,2618071,4189291,2828535,4321070,3183856,4689306,517702,4397492,3686267,3905013,4299673,4519412,4003248,6555466,3574260,6355304,532367,3666628,5571185,3386524,3198386,5226421,5072173,3272530,4240227,1758651,5750901,4595591,2939550,5383528,3325132,1360861,4524803,5780686,269332,3046399,1721200,4589117,4630050,1921156,2516007,422092,3353721,3161798,3570150,2945262,3236480,5542974,2040559,5523443,5472183,1167014,4315772,3910403,3025049,2427502,3120817,4812327,6032554,4784375,5182909,3203077,3683789,5720239,890359,4850053,4682751,3392500,6385730,1896224,4393668,2733422,5278679,4295693,3510033,6525102,625537,4240890,3747212,3699056,1748160,2864534,1733648,5370046,2242409,4906008,4242079,5130883,4185753,1472551,4345587,4898872,3624653,3155406,2964501,2454786,4494677,3958028,5806367,2624690,4931616,762912,3664639,3036268,3716406,5669028,7056774,2613515,6069058,3884277,4001380,2384763,6285802,4996178,4803360,2829276,4439318,3480991,5326179,6431124,3979302,5023920,4357242,3694869,5211530,4678456,4462939,1354874,4366611,3927660,4742657,6410662,3277122,5513418,5433701,6121017,4267552,2434006,5850893,4782933,3401171,4962603,4951023,5301106,4591627,3692724,4894410,4443741,3659093,3503721,4164730,6595970,5804869,4336840,4796874,4679845,5605936,4429455,6008820,4348420,3624951,5434195,4277645,5097181,5627257,2961648,1637664,2366811,3780274,4580533,5645382,4802361,1519379,267177,1853844,5076612,3613413,5247518,3757454,4322073,3549169,3964736,537540,2472264,4555490,5017478,5965790,579667,3053503,4265895,4025526,5835085,1516157,4544954,4472861,1562732,6198569,5302630,6117772,3357350,4407108,610676,3271173,3656544,1574214,5916234,586085,3316417,3716341,4063643,4220094,2871355,4895413,3330216,5423126,5979030,4794847,4813512,4088703,1544559,4928078,5361887,4062052,3279458,4455189,5163982,2823486,3746369,4597934,6806714,1079811,3930647,487216,4399868,2288760,5038552,5173399,3193730,2430035,5565394,2248265,5357765,3127840,518258,2887205,3385728,6576085,6406527,5027220,7023408,3364517,5257772,2877930,3650946,1921742,5406531,6383919,6965607,3196835,4381019,3050943,3793861,6670323,157823,5019367,6120075,116613,5406665,4403403,6001253,3548852,3443645,6811423,1772803,3172688,2548602,5161866,5733279,1632068,419192,3684450,1925686,5243957,6477348,3519616,4168876,6352434,3647189,2624984,671101,4320166,5105915,5981503,751562,5842941,4040654,3137575,3015729,2252397,4187796,5577325,6814418,5725778,5190083,1424573,3421235,3576979,3437112,6498048,1924731,466960,5776880,11260,909922,1311239,4112616,2461796,2713098,6054513,6126594,2147729,3224637,5697658,4096124,2400144,2665302,6944599,4514496,4426288,1244606,901988,2140933,3376398,3451129,5147652,3519798,2545551,2161563,4137692,4662429,548684,6555922,2934632,4718792,6307542,6068218,1150516,2965199,3125363,4941301,6296815,4662903,2927728,2273710,2672063,4139362,1260998,262474,51459,3536169,2781030,4651642,4359962,787281,1824944,2217913,3930017,870140,3278307,3250430,3190552,59989,2438359,4145363,3810472,3658949,1822333,4792487,2675493,2694140,2122828,4954208,1835348,4183365,4199092,942059,1856255,3050345,2425169,1248143,3347313,931391,4667778,4579653,416759,3039867,3260292,5801792,1884347,4969567,5056658,2811663,1492746,1486198,4648035,4547121,2647211,3263364,2712001,4634535,3072666,2109715,4466982,3298191,268504,3371841,2688681,4784472,332440,753577,4622542,4260268,693550,4232948,6208308,1382903,6383456,2087807,3138827,867878,4786465,4116758,576531,1915451,4211705,3315223,1882311,3627545,2915015,3510536,4130209,1766138,1140524,2694510,5611923,500260,3236719,1112947,3208039,3026978,3041528,6311239,2903084,6651326,3711267,3348489,3157371,5475509,3366337,3542026,3549338,4626188,3555989,1227050,240009,4155447,5788891,5111785,3283085,2663149,1988364,3201170,3426472,5858622,3685113,6198018,5223409,4262011,1024646,5899232,3687954,6647838,1099254,6475662,1471903,4062317,6269281,5383661,2502334,5918677,782168,225663,5114508,526700,3206960,2807762,3093351,6113435,3746359,4763592,3798550,4571115,1922726,2871000,3831783,1668544,1130752,547314,5864658,2538289,5169220,3264693,4681280,3672990,2440177,3120624,2277189,4004914,3028094,4940270,5869528,4802872,5991850,4111902,3864403,5823836,5628785,4248149,4150351,5104331,258783,1237528,4447715,4527477,5095093,2450186,6085960,3059042,4274076,3591006,5812998,5221339,5705549,4665646,5942597,6815078,4932969,6184325,4422937,4346573,5695243,4315546,6756616,4029746,6728270,5329729,5730116,6752393,5188529,6312645,5910813,4397291,6092863,4715582,6684726,6662522,6314964,6484965,2645854,5970270,6493126,4005098,3699440,3899387,4807871,6526364,5624991,4102552,6528644,6486946,5824270,5062487,6261869,6026758,6048457,4910415,5710413,4357574,5844147,5346984,6132950,6960816,2734192,5421997,6398963)
)